How to use column value as column header?

Question

I have a table like this:

ItemID    PartID   PartName   PartValue
  1         1        N1         v1
  1         2        N2         v2
  1         3        N3         v3
...
  2         1        N1          u1
  2         2        N2          u2
  2         3        N3          u3
...

Can I use a sql select statement to get following table?

ItemID    N1    N2    N3 ...
  1       v1    v2    v3 ...
  2       u1    u2    u3 ...
 ...

The problem is I don't know how many part names are there, so there might be also n4, n5, n6 ...so need dynamically select. I see some pivot examples but looks like it can be used when I know how many part names. If I don't know, how should I deal with this? Thanks!

Quick drive-by hint at a solution - What about using a stored prodecure with a for loop to generate a view? — Pimgd, Feb 29 '12 at 15:12

score 2 · Answer 1 · edited May 23 '17 at 11:55

2

If you can't predefine the columns in your output then PIVOT is not appropriate. Instead dynamic SQL (which should generally be avoided) is a way forward.

See SQL Server 2005 Pivot on Unknown Number of Columns

edited May 23 '17 at 11:55

Community

1
1

answered Feb 29 '12 at 15:11

kaj

5,133
2
21
18

score 1 · Accepted Answer · edited May 23 '17 at 12:21

1

This can be done using dynamic SQL to build a pivot statement. Awkward, ugly, but it works, and can work quite efficiently. Please review my first answer to this question for details.

edited May 23 '17 at 12:21

Community

1
1

answered Feb 29 '12 at 15:14

Philip Kelley

39,426
11
57
92

GarethD · Answer 3 · 2012-02-29T16:07:26.113

The below has been tested in SQL-Server, but I think the principals can be applied to whatever RDBMS you are using.

CREATE TABLE #t (ItemID INT, PartID INT, PartName VARCHAR(2), PartValue VARCHAR(2))
INSERT #t VALUES (1, 1, 'N1', 'V1'), (1, 2, 'N2', 'V2'), (1, 3, 'N3', 'V3'), (2, 1, 'N1', 'V1'), (2, 2, 'N2', 'V2'), (2, 3, 'N3', 'V3')

DECLARE @SQL VARCHAR(MAX)
SELECT  @SQL = ISNULL(@SQL, '') + 
                        ', (    SELECT  PartValue 
                                FROM    #t t' + Rownum + ' 
                                WHERE   t' + Rownum + '.ItemID = t.ItemID
                                AND     t' + Rownum + '.PartName = ''' + PartName + ''' 
                            ) [' + PartName + ']'
FROM    (   SELECT  PartName, CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY PartName)) [RowNum]
            FROM    #t
            GROUP BY PartName
        ) d
SET @SQL = 'SELECT ItemID' + @SQL + ' FROM (SELECT DISTINCT ItemID FROM #t) t'
EXEC (@SQL)

DROP TABLE #t

Note - This is likely to get inefficient if you have a a lot of data, with a lot fo columns

How to use column value as column header?

3 Answers3