CODEWITHSUNDEEP
sqlsql-server-2008
HelloWorld1
HelloWorld1

Reputation: 14108

Display the name of the column as data

If source table is:

City         1996-MAY  1996-june  1996-july 1996-aug  1996-sep  1996-okt
 -----------  --------  --------  --------  --------  --------  --------
New Orleans         6          3         3         3         3         3    
Los Angeles         4          3         3         3         3         3

select city, 1996-MAY, 1996-june, 1996-july, 1996-aug, 1996-sep, 1996-okt
  from a

The requested result is:

City         People    Date
-----------  --------  --------
New Orleans         6  1996-MAY
Los Angeles         4  1996-MAY  
New Orleans         3  1996-june
Los Angeles         3  1996-june  
New Orleans         3  1996-july 
Los Angeles         3  1996-july 
....            ...        .....

How should I do it?

This is not only one column, it's a lot of columns up to 2003-MAY.

Upvotes: 0

Views: 111

Answers (1)

GarethD
GarethD

Reputation: 69819

Use UNPIVOT:

SELECT  City, Date, People
FROM    T
        UNPIVOT 
        (   People 
            FOR Date IN ([1996-May], [1996-June], [1996-July])
        ) upvt

You could also do this dynamically:

DECLARE @SQL NVARCHAR(MAX) = ''

SELECT  @SQL = ',' + QUOTENAME(Name)
FROM    SYS.COLUMNS 
WHERE   OBJECT_ID = OBJECT_ID('TableName')  
AND     Name != 'City'

SET @SQL = 'SELECT  City, Date, People
            FROM    T
                    UNPIVOT 
                    (   People 
                        FOR Date IN (' + STUFF(@SQL, 1, 1, '') + ')
                    ) upvt'

EXECUTE SP_EXECUTESQL @SQL

Upvotes: 3

Related Questions