Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select rows into colums Expand / Collapse
Author
Message
Posted Thursday, February 28, 2013 6:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 3:04 AM
Points: 10, 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 :)

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

Post #1425020
Posted Thursday, February 28, 2013 6:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 2,434, Visits: 7,513
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



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1425025
Posted Thursday, February 28, 2013 6:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 3:04 AM
Points: 10, Visits: 129
Wow SSCrazy,

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

Thnx a lot!
Post #1425028
Posted Thursday, February 28, 2013 2:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 3:04 AM
Points: 10, 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 :)

Regards!
Post #1425281
Posted Friday, March 1, 2013 6:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 10, 2014 4:50 PM
Points: 324, Visits: 217
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.


Post #1425488
Posted Thursday, March 14, 2013 6:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 3:04 AM
Points: 10, 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 :))

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)


Post #1430920
Posted Thursday, March 14, 2013 8:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 2,434, Visits: 7,513
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.


Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1431011
Posted Tuesday, March 19, 2013 10:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:17 AM
Points: 2,434, Visits: 7,513
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!



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1432767
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse