asked on SQL Pivot Help
Hi Experts,
I have a table like this
Item# | Color | Ink |Sequence
123A | blue | A234 | 1
123A | red | B235 |2
123A | green| C236 |3
124B | red | A123 |1
124B | white | A124 |2
I would like the output to be like this:
Item# | Station1 | Station2 | Station3
123A | Color: blue |Color: red | Color: green
Ink: A234 | Ink: B235 |Ink: C236
124B | Color: red |Color: white| NULL
|Ink: A123 |Ink: A124
The sequence can be anywhere from 1 to 10.
In the output table the color and ink are separated by CHAR(10) so there should be one line returned for each unique item #
Thanks
-Morty
I have a table like this
Item# | Color | Ink |Sequence
123A | blue | A234 | 1
123A | red | B235 |2
123A | green| C236 |3
124B | red | A123 |1
124B | white | A124 |2
I would like the output to be like this:
Item# | Station1 | Station2 | Station3
123A | Color: blue |Color: red | Color: green
Ink: A234 | Ink: B235 |Ink: C236
124B | Color: red |Color: white| NULL
|Ink: A123 |Ink: A124
The sequence can be anywhere from 1 to 10.
In the output table the color and ink are separated by CHAR(10) so there should be one line returned for each unique item #
Thanks
-Morty
Microsoft SQL Server 2008Microsoft SQL Server 2005Microsoft SQL Server
Last Comment
Patrick Matthews
ASKER
I should add that I was hoping to create a view with the results.
Is the sequence only ever up to 3, or up to some fixed number?
Or does the query have to handle any number of them?
Or does the query have to handle any number of them?
ASKER
It can only be up to 15 (I said 10 in my original post but it can be 15 max)
SELECT DISTINCT t.[Item#],
(SELECT TOP 1 'Color: ' + t1.[Color] + CHAR(10) + 'Ink: ' + t1.Ink
FROM SomeTable t1
WHERE t1.[Item#] = t.[Item#] AND t1.Sequence = 1) AS Station1,
(SELECT TOP 1 'Color: ' + t2.[Color] + CHAR(10) + 'Ink: ' + t2.Ink
FROM SomeTable t2
WHERE t2.[Item#] = t.[Item#] AND t1.Sequence = 2) AS Station2,
(SELECT TOP 1 'Color: ' + t3.[Color] + CHAR(10) + 'Ink: ' + t3.Ink
FROM SomeTable t3
WHERE t3.[Item#] = t.[Item#] AND t3.Sequence = 1) AS Station3,
... you get the idea :)
(SELECT TOP 1 'Color: ' + t15.[Color] + CHAR(10) + 'Ink: ' + t15.Ink
FROM SomeTable t15
WHERE t15.[Item#] = t.[Item#] AND t1.Sequence = 15) AS Station15
FROM SomeTable t
(SELECT TOP 1 'Color: ' + t1.[Color] + CHAR(10) + 'Ink: ' + t1.Ink
FROM SomeTable t1
WHERE t1.[Item#] = t.[Item#] AND t1.Sequence = 1) AS Station1,
(SELECT TOP 1 'Color: ' + t2.[Color] + CHAR(10) + 'Ink: ' + t2.Ink
FROM SomeTable t2
WHERE t2.[Item#] = t.[Item#] AND t1.Sequence = 2) AS Station2,
(SELECT TOP 1 'Color: ' + t3.[Color] + CHAR(10) + 'Ink: ' + t3.Ink
FROM SomeTable t3
WHERE t3.[Item#] = t.[Item#] AND t3.Sequence = 1) AS Station3,
... you get the idea :)
(SELECT TOP 1 'Color: ' + t15.[Color] + CHAR(10) + 'Ink: ' + t15.Ink
FROM SomeTable t15
WHERE t15.[Item#] = t.[Item#] AND t1.Sequence = 15) AS Station15
FROM SomeTable t
ASKER
First of all, thanks a lot, this works but...
Is there no way to do it using the pivot function or a more efficient way?
Right now it's not that bad, it takes about 3-4 seconds but the table currently only has 30k rows and grows by up to several hundred per day.
Is there no way to do it using the pivot function or a more efficient way?
Right now it's not that bad, it takes about 3-4 seconds but the table currently only has 30k rows and grows by up to several hundred per day.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Wow, thanks a lot. That reduced the time down to .289 seconds from 3.2
Glad to help :)