SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


select rows into colums


select rows into colums

Author
Message
SQLServerForum.nl
SQLServerForum.nl
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 129
Dear T-sqlérs,

I have a test table (see script below) with the following result

name length
balk1 7
balk1 6
balk1 9
stof1 6
stof2 6
stof3 6
stof4 6
stof5 6
stof5 7
stof6 6
stof7 6
stof8 6
stof9 6
stof9 7
stof10 6
stof11 6
stof12 6

Now I would like the result to be like this:

balk1 stof1 stof2 stof3 stof4 stof5 stof 6 stof 7 stof 8 stof 9 stof10 stof 11 stof 12
6 6 6 6 6 6 6 6 6 6 6 6 6
7 7 7
9

How should my query be?

Thnx a lot in advance for your help Smile

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


Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8962 Visits: 8490
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

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
SQLServerForum.nl
SQLServerForum.nl
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 129
Wow SSCrazy,

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

Thnx a lot!
SQLServerForum.nl
SQLServerForum.nl
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 129
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 Smile

Regards!
buddy__a
buddy__a
Right there with Babe
Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)Right there with Babe (722 reputation)

Group: General Forum Members
Points: 722 Visits: 257
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
SQLServerForum.nl
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 129
Dear old hand,

Thank you for your response! What I actually want is to calculatie the number of items the customer has to buy:

item Balk1 Stof1 Stof2 Stof3 Stof4 Stof5 Stof6 Stof7 Stof8
number of items needed 4 6 2 4 4 1 16 12 4
length of each item (mm) 5138,15 1478 2000 2698,24 1479,36 3350 800 1333 1473
available length (mm) 6000 6000 6000 6000 6000 6000 6000 6000 6000
available length (mm) 7000 7000 7000 NULL NULL NULL NULL NULL NULL
available length (mm) 9000 9000 9000 NULL NULL NULL NULL NULL NULL
Suggested length 6000 9000 6000 6000 6000 6000 6000 6000 6000
[color=red]nr of items to buy 4 1 1 2 1 1 3 3 1[/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 Smile)

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)



Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8962 Visits: 8490
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.


Forever trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8962 Visits: 8490
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!


Forever trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search