How to convert SQL Server columns into rows?

Question

I need help to switch columns with rows in SQL. Need to turn this:

+------------+------------+-------------+------+
|    Date    | Production | Consumption | .... |
+------------+------------+-------------+------+
| 2017-01-01 |        100 |        1925 |      |
| 2017-01-02 |        200 |        2005 |      |
| 2017-01-03 |        150 |        1998 |      |
| 2017-01-04 |        250 |        2200 |      |
| 2017-01-05 |         30 |         130 |      |
|...         |            |             |      |
+------------+------------+-------------+------+

into this:

+------------+------------+------------+------------+------------+-----+
| 01-01-2017 | 02-01-2017 | 03-01-2017 | 04-01-2017 | 05-01-2017 | ... |
+------------+------------+------------+------------+------------+-----+
|        100 |        200 |        150 |        250 |         30 |     |
|       1925 |       2005 |       1998 |       2200 |        130 |     |
+------------+------------+------------+------------+------------+-----+

Can someone help me? Should I use PIVOT?

EDIT: I've tried using some suggestions like PIVOT and UNPIVOT, but I could not achieve the expected result.

I've tried:

SELECT *
FROM (
    SELECT date, Consumption
    FROM Energy
    where date < '2017-02-01'
) r
pivot (sum(Consumption) for date in ([2017-01-01],[2017-01-02],[2017-01-03]....)) c
order by 1

However with the above query I only managed to get some of what I need,

+------------+------------+------------+------------+------------+-----+
| 01-01-2017 | 02-01-2017 | 03-01-2017 | 04-01-2017 | 05-01-2017 | ... |
+------------+------------+------------+------------+------------+-----+
|        100 |        200 |        150 |        250 |         30 |     |
+------------+------------+------------+------------+------------+-----+

I need to have production and consumption, all in the same query, but I can only get one of them.

Is it possible to put more than one column in PIVOT? I've tried, but unsuccessfully.

When posting a question, it's important to ask a question. That seems a silly, and obvious to say, but you haven't actually asked a question. What's is your question, and what have you tried so far to answer it? — Thom A, Feb 22 '19 at 15:17
@Larnu I'm sorry, I just pressed the button to create my question while I was still writing the question. I just edited my question. — JBranches, Feb 22 '19 at 16:02
You say you've tried, but you've omitted those attempts. it's important to include those in your question as well. Thanks. — Thom A, Feb 22 '19 at 16:04
I think your question can be reopened if you will put your query what you have tried. — Suraj Kumar, Feb 22 '19 at 16:30
You could just UNION your current PIVOT query with another that gets Production. — Tab Alleman, Feb 22 '19 at 18:39
Hello @TabAlleman, thank you, that worked. Do you have some suggestion to make the query more dynamic? instead of having to put all the dates in the pivot part. — JBranches, Feb 25 '19 at 09:30
Dynamic pivot: https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Tab Alleman, Feb 25 '19 at 14:38

score 0 · Answer 1 · answered Mar 12 '19 at 15:09

You can achieve the desired output with dynamic sql, but be aware of performance and security problems (i.e. SQL injection) of this approach.

--create test table 
CREATE TABLE dbo.Test ( 
      [Date]      date 
    , Production  int 
    , Consumption int 
) 

--populate test table with values 
insert into dbo.Test 
values 
 ('2017-01-01', 100, 1925) 
,('2017-01-02', 200, 2005) 
,('2017-01-03', 150, 1998) 
,('2017-01-04', 250, 2200) 
,('2017-01-05',  30,  130) 

--table variable that will hold the names of all columns to pivot 
declare @columNames table (ColumnId int identity (1,1), ColumnName varchar(255)) 
--variable that will hold the total number of columns to pivot 
declare @columnCount int 
--variable that will be used to run through the columns to pivot 
declare @counter int = 1 
--this variable holds all column names 
declare @headers nvarchar(max) = '' 
--this variable contains the TSQL dinamically generated 
declare @sql nvarchar(max) = '' 

--populate list of columns to pivot 
insert into @columNames 
select COLUMN_NAME 
from INFORMATION_SCHEMA.COLUMNS 
where 
        TABLE_NAME   = 'test' 
    and TABLE_SCHEMA = 'dbo' 
    and COLUMN_NAME  <>'date' 

--populate column total counter 
select @columnCount = count(*) from @columNames 

--populate list of headers of the result table 
select @headers = @headers + ', ' + quotename([Date]) 
from dbo.Test 

set @headers = right(@headers, len(@headers) - 2) 

--run through the table containing the columns names and generate the dynamic sql query 
while @counter <= @columnCount 
begin 
    select @sql = @sql + ' select piv.* from (select [Date], ' 
        + quotename(ColumnName) + ' from dbo.Test) p pivot (max(' 
        + quotename(ColumnName) + ') for [Date] in (' 
        + @headers + ') ) piv ' 
    from @columNames where ColumnId = @counter 

    --add union all except when we are concatenating the last pivot statement
    if @counter < @columnCount 
        set @sql = @sql + ' union all' 

    --increment counter
    set @counter = @counter + 1 
end 

--execute the dynamic query
exec (@sql)

Result:

Now if you add a column and some more rows:

--create test table 
CREATE TABLE [dbo].[Test] ( 
      [Date]      date 
    , Production  int 
    , Consumption int 
    , NewColumn   int 
) 

--populate test table with values 
insert into [dbo].[Test] 
values 
 ('2017-01-01', 100, 1925 , 10) 
,('2017-01-02', 200, 2005, 20) 
,('2017-01-03', 150, 1998, 30) 
,('2017-01-04', 250, 2200, 40) 
,('2017-01-05', 30, 130  , 50) 
,('2017-01-06', 30, 130  , 60) 
,('2017-01-07', 30, 130  , 70)

this is the result:

How to convert SQL Server columns into rows?

1 Answers1