February 28, 2013 at 6:19 am
Dear T-sqlérs,
I have a test table (see script below) with the following result
namelength
balk17
balk16
balk19
stof16
stof26
stof36
stof46
stof56
stof57
stof66
stof76
stof86
stof96
stof97
stof106
stof116
stof126
Now I would like the result to be like this:
balk1stof1stof2stof3stof4stof5stof 6stof 7stof 8stof 9stof10stof 11stof 12
6666666666666
777
9
How should my query be?
Thnx a lot in advance for your help
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[test](
[name] [varchar](50) NULL,
[length] [int] NULL
) ON [PRIMARY]
GO
insert into test (name, length) values ('balk1', 7)
insert into test (name, length) values ('balk1', 6)
insert into test (name, length) values ('balk1', 9)
insert into test (name, length) values ('stof1', 6)
insert into test (name, length) values ('stof2', 6)
insert into test (name, length) values ('stof3', 6)
insert into test (name, length) values ('stof4', 6)
insert into test (name, length) values ('stof5', 6)
insert into test (name, length) values ('stof5', 7)
insert into test (name, length) values ('stof6', 6)
insert into test (name, length) values ('stof7', 6)
insert into test (name, length) values ('stof8', 6)
insert into test (name, length) values ('stof9', 6)
insert into test (name, length) values ('stof9', 7)
insert into test (name, length) values ('stof10', 6)
insert into test (name, length) values ('stof11', 6)
insert into test (name, length) values ('stof12', 6)
select * from test
SET ANSI_PADDING OFF
GO
February 28, 2013 at 6:37 am
I guess something like this: -
DECLARE @sql NVARCHAR(MAX);
SELECT @sql='SELECT '+CHAR(13)+CHAR(10)+
STUFF((SELECT ','+CHAR(13)+CHAR(10)+'NULLIF(MAX(CASE WHEN [name] = '+CHAR(39)+[name]+CHAR(39)+
' THEN [length] ELSE '+CHAR(39)+CHAR(39)+' END),'+CHAR(39)+CHAR(39)+
') AS '+QUOTENAME([name])
FROM (SELECT DISTINCT [name]
FROM test
)a([name])
ORDER BY LEN([name]),[name]
FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)'),1,3,'')+CHAR(13)+CHAR(10)+'FROM [dbo].[test]'+
CHAR(13)+CHAR(10)+'GROUP BY [length];';
EXECUTE sp_executesql @sql;
Which returns: -
balk1 stof1 stof2 stof3 stof4 stof5 stof6 stof7 stof8 stof9 stof10 stof11 stof12
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
6 6 6 6 6 6 6 6 6 6 6 6 6
7 NULL NULL NULL NULL 7 NULL NULL NULL 7 NULL NULL NULL
9 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
February 28, 2013 at 6:44 am
Wow SSCrazy,
Thanks for your quick answer, this is exactly what i need!!
Thnx a lot!
February 28, 2013 at 2:26 pm
Dear SSCrazy,
I am very impressed by your solution, so i have been looking at it for quite a bit.
What i was wondering (but can't figure it out), if you insert two more rows:
insert into test (name, length) values ('stof13', 7)
insert into test (name, length) values ('stof13', 8)
when i run your solution, the 7 and 8 are in the second and third row. Could it be possible to get the 7 and 8 in row 1 and 2?
I hope you understand what i mean
Regards!
March 1, 2013 at 6:58 am
This is where you're going to have to give us more information about what you actually want. Cadavre's solution produces one row for each distinct value of length and since your new stof13 variable is the only one with a value of 8 then that creates a new row for 8's and only stof13 is populated with it. So, where do you think the stof13 value of 8 should go and why? Because based on your original post a value of 8 does not belong in a row with 6's or 7's. My guess is that somehow balk1 is the key variable that all the stof variables are related to but you haven't told us how they are to be related.
March 14, 2013 at 6:40 am
Dear old hand,
Thank you for your response! What I actually want is to calculatie the number of items the customer has to buy:
itemBalk1Stof1Stof2Stof3Stof4Stof5Stof6Stof7Stof8
number of items needed46244116124
length of each item (mm)5138,15147820002698,241479,36335080013331473
available length (mm)600060006000600060006000600060006000
available length (mm)700070007000NULLNULLNULLNULLNULLNULL
available length (mm)900090009000NULLNULLNULLNULLNULLNULL
Suggested length600090006000600060006000600060006000
nr of items to buy411211331[/color
The length they need are calculated and also the number of items they need. Now depending on the available lengths we have in stock, how should I calculate the nr of items to buy (I now did it with my head :))
Thanx in advance for all your help!
As I don't get a nice outlined table, i made another test table:
CREATE TABLE [dbo].[testtest](
[item] [varchar](50) NULL,
[balk1] [varchar](50) NULL,
[stof1] [varchar](50) NULL,
[stof2] [varchar](50) NULL,
[stof3] [varchar](50) NULL,
[stof4] [varchar](50) NULL,
[stof5] [varchar](50) NULL,
[stof6] [varchar](50) NULL,
[stof7] [varchar](50) NULL,
[stof8] [varchar](50) NULL,
) ON [PRIMARY]
GO
insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8)
values ('number of items needed', 4, 6, 2, 4, 4, 1, 16, 12, 4)
insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8)
values ('length of each item (mm)', 5138, 1478, 2000, 2698, 1479, 3350, 800, 1333, 1473)
insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8)
values ('available length (mm)', 6000, 6000, 6000, 6000, 6000, 6000, 6000, 6000, 6000)
insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8)
values ('available length (mm)', 7000, 7000, 7000, null, null, null, null, null, null)
insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8)
values ('available length (mm)', 9000, 9000, 9000, null, null, null, null, null, null)
insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8)
values ('Suggested length', 6000, 9000, 6000, 6000, 6000, 6000, 6000, 6000, 6000)
insert into testtest ([item], [balk1], [stof1], [stof2], [stof3], [stof4], stof5, stof6, stof7, stof8)
values ('nr of items to buy', 4, 1, 1, 2, 1, 1, 3, 3, 1)
March 14, 2013 at 8:31 am
We have a software release where I work which is imminent. This has reduced my activity on these forums considerably, which is why I've not posted any replies. Some time next week, I should have more time available so I'll be able to give you a hand then if no-one else has answered.
March 19, 2013 at 10:26 am
Hey,
I finally had 5 minutes to read through your replies here and have realised that you've completely changed the requirements. So, what we need from you is sample data and expected results based on your sample data. From there, I'm sure it'll be a fairly trivial task but right now you've got people shooting in the dark which is why no-one else has given you a ready made answer.
Thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy