Split Rows data to Columns in SQL

Question

I have a table with the following data

Date            Value
---------------------
2014-01-01        20
2014-01-02        18
2014-01-03        06
2014-02-01        22
2014-02-02        162
2014-02-03        16

Here i have first 3 dates data of 2 months. I have get the output as

Date  Jan 2014  Feb 2014
-------------------------
 01    20          06
 02    18          162
 03    06          16

hi, i tried pivot but pivot can give individual date tranpose. I dont need to transpose each data. Just take the second month value as new column. — Akhil, Apr 21 '14 at 12:02

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

Test Data

DECLARE @TABLE TABLE([Date] DATE,Value INT)
INSERT INTO @TABLE VALUES
('2014-01-01',20)  ,('2014-01-02',18),
('2014-01-03',06)  ,('2014-02-01',22),
('2014-02-02',162) ,('2014-02-03',16)

Query

SELECT * FROM (
SELECT DAY([Date]) AS [Date]
      ,LEFT(DATENAME(MONTH,[Date]), 3)
       + ' ' + CAST(YEAR([Date]) AS NVARCHAR(4)) Mon_YYYY
      , Value
FROM @TABLE ) t
PIVOT (SUM(Value)
       FOR Mon_YYYY
       IN ([Jan 2014],[Feb 2014])
       )P

Result Set

╔══════╦══════════╦══════════╗
║ Date ║ Jan 2014 ║ Feb 2014 ║
╠══════╬══════════╬══════════╣
║    1 ║       20 ║       22 ║
║    2 ║       18 ║      162 ║
║    3 ║        6 ║       16 ║
╚══════╩══════════╩══════════╝

You will need to use Dynamic sql to unpivot the actual data as there can be many values in a column and this solution isnt really practical for real life problems but at least it gives you some idea how to go about it. See this How to pivot unknown number of columns & no aggregate in SQL Server? to learn how to dynamically pivot rows.

score 0 · Answer 2 · answered Apr 21 '14 at 12:13

You can use PIVOT as below

declare @t table (sdate date,val int)
insert into @t values('2014-01-01',        20)
insert into @t values('2014-01-02',        18)
insert into @t values('2014-01-03',        06)
insert into @t values('2014-02-01',        22)
insert into @t values('2014-02-02',        162)
insert into @t values('2014-02-03',        16)

select * from 
(
select datepart(d,sdate) sday,DATENAME(MONTH, sdate)+' '+cast(datepart(YYYY,sdate) as varchar) yearMonth,val from @t
) t
pivot 
(
sum(val)
FOR YearMonth IN ([January 2014],[February 2014])
) AS PivotTable;

and this is the result

sday January 2014 February 2014
1        20       22
2        18       162
3        6        16

Split Rows data to Columns in SQL

2 Answers2