How to transpose a table in sql

Question

I'm looking for an easy way to perform this transpose:

date    ID  Vr    val 
1.1.14   1   2000  50
1.1.14   2   2000  60
1.1.14   2   2001  100

date ID  vr2000 vr2001
1.1.14 1   50      0
1.1.14 2   60     100

I have something like 83 variables and data of over 1m records. I want to concate "vr" to the index variable in the row. Any suggestions how can I do that?

Possible duplicate of [SQL transpose full table](http://stackoverflow.com/questions/15297809/sql-transpose-full-table) — MusicLovingIndianGirl, Jun 22 '16 at 12:33
Select the raw data from the table. Use a programming language (Java, C#, PHP, whatever) to display the data in the desired format. — Thorsten Kettner, Jun 22 '16 at 12:33

Yared · Answer 1 · 2016-08-20T20:10:19.977

This can be done by using dynamic sql query and execute the dynamically built query using sp_executesql stored precedure.

-- Create table Variable
CREATE TABLE [Variable](
    [date] [nvarchar](50) NULL,
    [ID] [int] NULL,
    [vr] [int] NULL,
    [val] [int] NULL
)

--Insert sample records
INSERT [dbo].[Variable] ([date], [ID], [vr], [val]) 
VALUES  (N'1.1.14', 1, 2000, 50),
        (N'1.1.14', 2, 2000, 60),
        (N'1.1.14', 2, 2001, 100)

GO
--QUERY
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnNamesInPivot AS NVARCHAR(MAX);
DECLARE @ColumnNamesInSelect AS NVARCHAR(MAX);

--Get distinct values of PIVOT Column 
SELECT TOP 100 PERCENT
        @ColumnNamesInPivot = ISNULL(@ColumnNamesInPivot + ',', '')
        + QUOTENAME(vr),
        @ColumnNamesInSelect = ISNULL(@ColumnNamesInSelect + ',', '')
        + 'ISNULL(' + QUOTENAME(vr) + ',0) AS [vr' + CAST(vr AS NVARCHAR(50))
        + ']'
FROM    ( SELECT DISTINCT
                    vr
          FROM      Variable
        ) AS P
ORDER BY vr ASC;

--Prepare the PIVOT query using the dynamic query
SET @DynamicPivotQuery = N'Select date,ID,' + @ColumnNamesInSelect + ' 
            FROM    ( SELECT * 
          FROM      Variable
        ) AS SourceTable PIVOT( MAX(val) FOR vr IN (' + @ColumnNamesInPivot
    + ') ) AS PVTTable';

--PRINT @DynamicPivotQuery;
EXEC sp_executesql @DynamicPivotQuery;

The result looks like:

How to transpose a table in sql

1 Answers1