Advanced SQL HELP: transform rows to columns

I have a table of 1000+ rows with 100+ different items with each Id having max 10 items

Id item
1 a
1 b
2 c
2 a
2 d
3 b
3 z

each item has an order in a different lookup table
item item_seq
a 1
b 2
c 3
d 4

The item columns (below) need to use the item order from lookup table, so the output table is as follows

Id item item2 item3
1 a b
2 a c d
3 b z

PLEASE HELP !!!Hello,
I am a little bit confused about your target but what I can catch is that you are asking how to make an output table like the one you demonstrated at the end. If it is so, I think that the table design itself is not correct and what you need is to make a one to many relation ship table composed of 2 fields that looks like the following

Id Item
1 a
1 b
2 a
2 c
2 d
3 b
3 Z
and so on

and by this way you can insert as many as items as you like with each Id

Thanks|||Thanks vm for replying Rasha.

To make matters simpler, I exclude the item order table since I found a way of doing that.
So basically the input table is
Id Item
1 a
1 b
2 a
2 c
2 d
3 b
3 Z

and the output table I need to come up with should be like

Id Item1 Item2 Item3
1 a b
2 a c d
3 b z

The CASE statement is diffucilt to use since there are 100+ different items (and new ones are added all the time) and each id can have 1 to 9 items, so columns Item1 to Item9 per id are needed.

Any ideas ?

Originally posted by rasha
Hello,
I am a little bit confused about your target but what I can catch is that you are asking how to make an output table like the one you demonstrated at the end. If it is so, I think that the table design itself is not correct and what you need is to make a one to many relation ship table composed of 2 fields that looks like the following

Id Item
1 a
1 b
2 a
2 c
2 d
3 b
3 Z
and so on

and by this way you can insert as many as items as you like with each Id

Thanks|||What you are asking for is called a cross tab query. The pivot is best handled on the client if at all possible, but it can also be done in SQL when you can't do it on the client.

-PatP|||Hi, I do not know much about Cross tab query but I think the following dissccusion may help you:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19008|||Assuming that your item values are sortable for each id (that there aren't any NULL values and no duplicates), you can use:CREATE TABLE dbo.table00 (
id INT NOT NULL
, item CHAR(1) NOT NULL
)

INSERT INTO dbo.table00 (id, item)
SELECT 1, 'a'
UNION ALL SELECT 1, 'b'
UNION ALL SELECT 2, 'a'
UNION ALL SELECT 2, 'c'
UNION ALL SELECT 2, 'd'
UNION ALL SELECT 3, 'b'
UNION ALL SELECT 3, 'z'

SELECT a.id, Min(a.item)
+ Coalesce(', ' + Min(b.item), '')
+ Coalesce(', ' + Min(c.item), '')
FROM dbo.table00 AS a
LEFT JOIN dbo.table00 AS b
ON (b.id = a.id
AND a.item < b.item)
LEFT JOIN dbo.table00 AS c
ON (c.id = a.id
AND b.item < c.item)
GROUP BY a.id
ORDER BY a.id

DROP TABLE dbo.table00-PatP|||Thanks very much for your reply PAT.

I have to say your approach with the LEFT join and item> comparisons
is simple and clever.
However bearing in mind that there are 100+ different items, doing 100+ left joins...I don't how efficient will that be...
Thanks very much anyway.|||Originally posted by dim2000
However bearing in mind that there are 100+ different items, doing 100+ left joins...I don't how efficient will that be... Try it. You might be shocked (I know that I was).

A cross tab is still better handled on the client when that is possible, but the server does a better job than I thought that it would.

-PatP|||Thanks for your reply Pat.

The left joins worked quite fast actually, on the server that is not the client.

Do you know if I could use the Case or ISNULL statements for this.

e.g. CASE when item='a' THEN item1 ELSE 'None' END AS 'MCQ Raw',

I used the case statement before but only for amounts that needed to be summed up, when I tried to use it with text fields I could not get it to work,
Any ideas?