Display table

[borgkling2003]

This is my current table

ID | Q_ID | A_ID
----------------
11 | 11 | 100
11 | 12 | 200
11 | 13 | 333
11 | 14 | 344
12 | 11 | 555
12 | 15 | 557
12 | 16 | 577

Can someone show me a QUERY how to display it in the following format:

ID |11 | 12 | 13 | 14 | 15 |16
-------------------------------
11 |100|200 |333 |344 | |
12 |555| | | |557 |577

 

[manoher]

Query:

select ID,[11],[12],[13],[14],[15],[16] from Temp_ID pivot ( sum(A_ID) for Q_ID in ([11],[12],[13],[14],[15],[16])) as pvt order by ID

 

[mrdenny]

That'll work for SQL 2005. In SQL 2000 you'll be doing it by hand.

select id, 
     max(case when q_id = 11 then a_id end) '11',
     max(case when q_id = 12 then a_id end) '12',
...
from TableName

The SQL 2005 example code looks like this.

SELECT VendorID, [11], [12], [13], [14], [15], ...
FROM 
(SELECT Q_ID, A_ID FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (Q_ID)
FOR Q_ID IN
( [11], [12], [13], [14], [15], ... )
) AS pvt
ORDER BY Q_ID

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com