select rows into colums

Question

Post reply

select rows into colums

SQLServerForum.nl

SSChasing Mays

Points: 660
More actions
February 28, 2013 at 6:19 am

#274724

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

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

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

SQLServerForum.nl SSChasing Mays Points: 660 More actions · Answer 2

Wow SSCrazy,

Thanks for your quick answer, this is exactly what i need!!

Thnx a lot!

SQLServerForum.nl SSChasing Mays Points: 660 More actions · Answer 3

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!

buddy__a Hall of Fame Points: 3222 More actions · Answer 4

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.

SQLServerForum.nl SSChasing Mays Points: 660 More actions · Answer 5

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