Unpivot columns in a table

Turn a list of columns into rows.

1
2
3
4
5
6
7
8
9
10
11
12
13
--Create a test table
CREATE TABLE Test
(
	RowID INT IDENTITY(1,1),
	Col1 VARCHAR(50),
	Col2 VARCHAR(50),
	Col3 VARCHAR(50)
)
 
--Insert some values
INSERT INTO Test (Col1, Col2, Col3) VALUES ('A', 'B', 'C'), 
                                           ('D', 'E', 'F'), 
                                            ('G', 'H', 'I')

We get a table like this

Col2	COLUMN	VALUE
E	RowID	2
E	Col1	D
E	Col3	F

I created a stored procedure to do the UNPIVOT

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
CREATE PROCEDURE UnpivotTable
	@tableName nvarchar(4000), @unpivotColumn nvarchar(4000), 
        @unpivotColumnWhere nvarchar(100)
AS
BEGIN
     -- SET NOCOUNT ON added to prevent extra result sets from
     -- interfering with SELECT statements.
     SET NOCOUNT ON;
 
     DECLARE @paramDefinition nvarchar(4000)
     DECLARE @cListRet nvarchar(MAX), @cListWithCastRet nvarchar(MAX)
     DECLARE @SQL nvarchar(MAX)
 
     SELECT @cListRet = '', @cListWithCastRet = ''
 
     --Check for server / schema / table format
     IF ((PATINDEX('%.%.%', @tableName) > 0) 
           AND (LEFT(@tableName, LEN(DB_NAME())) <> DB_NAME())) 
     BEGIN
	RAISERROR('Table must be located in current database', 16, 1)
     END
 
    --Create a comma delimited list of columns for the 
    --specified table (minus the unpivoted one)
    ;WITH CTE AS 
    (
	SELECT c.name 
	FROM sys.COLUMNS c 
           INNER JOIN sys.TABLES t ON c.object_id = t.object_id 		
	WHERE 
        t.name = RIGHT(@tableName, LEN(@tableName)-CHARINDEX('.', @tableName)) 
              AND c.name <> @unpivotColumn 
    )
    SELECT @cListRet = @cListRet + name + N',' FROM CTE		
 
    --Remove the last comma
    SET @cListRet = LEFT(@cListRet, LEN(@cListRet) - 1)	
 
    ;WITH CTE AS
    ( 
        SELECT VALUE FROM dbo.fn_Split(@cListRet, ',') 
    ) 
    SELECT @cListWithCastRet = @cListWithCastRet + 'ISNULL(CAST(' + 
         CTE.VALUE + ' AS nvarchar(MAX)), '''') AS ' + CTE.VALUE + ', '
    FROM CTE
 
    --Remove the last comma  
    SET @cListWithCastRet = LEFT(@cListWithCastRet, LEN(@cListWithCastRet) - 1)
    ---------------------------
 
    SET @SQL = N'SELECT ' + @unpivotColumn + 
               N', Col AS [Column], Pivot_Col as [Value]' +
	       N' FROM (SELECT ' + @unpivotColumn + N', ' + @cListWithCastRet +
	       N' FROM ' + @tableName + N' WHERE ' + @unpivotColumn + N' =''' + 
               @unpivotColumnWhere + N''') T ' +
	       N'UNPIVOT ' +
	       N'(Pivot_Col FOR Col IN (' + @cListRet + N')' +
	       N') AS TT;'
 
    EXEC sp_executesql @SQL
 
END
 
GO

If I run the SP:

EXEC dbo.UnpivotTable 'dbo.Test', 'Col2', 'E'

You get this:

Col2	COLUMN	VALUE
E	RowID	2
E	Col1	D
E	Col3	F

3 thoughts on “Unpivot columns in a table

  1. Your post, Unpivot columns in a table | SQL Press Blog, is really well written and insightful. Glad I found your website, warm regards from Eugenio!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>