rows to columns

Question

rows to columns

Charmer

SSChampion

Points: 14903
More actions
June 27, 2016 at 11:36 pm

#314673

Hi Friends,
I am trying pivot the rows but I am struggling to get the query. Here is the sample output.
/* ACTUAL TABLE */
DECLARE @Actual TABLE (ReportDate date , DatabaseName varchar(10), ReportID int , TotalSum bigint )
INSERT INTO @Actual
SELECT '2016-06-26', 'Test1' , 1, 150
UNION ALL
SELECT '2016-06-27', 'Test2' , 2, 200
UNION ALL
SELECT '2016-06-28', 'Test3' , 3, 150
UNION ALL
SELECT '2016-06-29', 'Test4' , 4, 230
UNION ALL
SELECT '2016-06-30', 'Test5' , 5, 100
SELECT * FROM @Actual
/* EXPECTED OUTPUT */
DECLARE @Pivot TABLE (ReportDate varchar(12) , [2016-06-26] varchar(10), [2016-06-27] varchar(10), [2016-06-28] varchar(10),[2016-06-29] varchar(10),[2016-06-30] varchar(10) )
INSERT INTO @Pivot
SELECT 'DatabaseName', 'Test1' , 'Test2', 'Test3' , 'Test4' , 'Test5'
UNION ALL
SELECT 'ReportID', '1', '2', '3', '4', '5'
UNION ALL
SELECT 'TotalSum', '150', '200', '150', '230' , '100'
SELECT * FROM @Pivot

The ReportDate values can be anything. I want dynamic.
Please help me , friends.
Thanks,
Charmer

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

Well, the Clue is in you Sample data generation query. if you build your query according to that logic you will be able to get your desired output.

something like this:

WITH CTE (ReportDate, colname, Value)

AS

(

select ReportDate, 'Database Name', cast(DatabaseName as varchar(500))

from @Actual

UNION ALL

select ReportDate, 'ReportID', cast(ReportID as varchar(500))

from @Actual

UNION ALL

select ReportDate, 'TotalSum', cast(TotalSum as varchar(500))

from @Actual

)

select *

from

(

select c.ReportDate, c.colname, c.Value

from cte c

) t1

Pivot

(

max(value) for t1.ReportDate IN ([2016-06-26], [2016-06-27], [2016-06-28],[2016-06-29],[2016-06-30])

) as t2

Hope it helps.

Charmer SSChampion Points: 14903 More actions · Answer 2

Charmer

SSChampion

Points: 14903

June 28, 2016 at 5:50 am

#1887237

It worked. Thank you so much.

Thanks,
Charmer

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

Charmer (6/27/2016)
The ReportDate values can be anything. I want dynamic.

Please see the following article for a simple method to make such reports dynamic.

http://www.sqlservercentral.com/articles/Crosstab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Y.B. SSChampion Points: 11649 More actions · Answer 4

Since I was already working on it here is a dynamic crosstab example Jeff was talking about.

DECLARE @Actual TABLE (ReportDate date , DatabaseName varchar(10), ReportID int , TotalSum bigint )

DECLARE @sql VARCHAR(MAX)

INSERT INTO @Actual

SELECT '2016-06-26', 'Test1' , 1, 150

UNION ALL

SELECT '2016-06-27', 'Test2' , 2, 200

UNION ALL

SELECT '2016-06-28', 'Test3' , 3, 150

UNION ALL

SELECT '2016-06-29', 'Test4' , 4, 230

UNION ALL

SELECT '2016-06-30', 'Test5' , 5, 100

--SELECT * FROM @Actual

SELECT ReportDate, 'DatabaseName' AS Colname, cast(DatabaseName AS VARCHAR(500)) AS Value

INTO #TEST

FROM @Actual

UNION ALL

SELECT ReportDate, 'ReportID' AS Colname, cast(ReportID AS VARCHAR(500)) AS Value

FROM @Actual

UNION ALL

SELECT ReportDate, 'TotalSum' AS Colname, cast(TotalSum AS VARCHAR(500)) AS Value

FROM @Actual

;WITH DistinctDates (n) AS

(

SELECT DISTINCT ReportDate FROM #TEST

)

SELECT @sql =

'SELECT Colname AS ReportDate' +

(SELECT ' , MAX(CASE WHEN ReportDate = ' + '''' + CAST( n AS VARCHAR(10)) + '''' + ' THEN Value END) AS ' + '''' + CAST( n AS VARCHAR(10)) + ''''

FROM DistinctDates FOR XML PATH(''),TYPE).value('./text()[1]', 'nvarchar(MAX)') +

' FROM #TEST' +

' GROUP BY Colname'

EXECUTE(@sql)

DROP TABLE #TEST

Cheers,

SELECT quote FROM brain WHERE original = 1
0 rows returned

durga.palepu Hall of Fame Points: 3530 More actions · Answer 5

durga.palepu

Hall of Fame

Points: 3530

June 28, 2016 at 12:36 pm

#1887411

Excellent, I learned this technique from you.

durga.palepu Hall of Fame Points: 3530 More actions · Answer 6

Yet another good alternative to learn but little complex compared to pivot solution.

Y.B. SSChampion Points: 11649 More actions · Answer 7

durga.palepu (6/28/2016)
Yet another good alternative to learn but little complex compared to pivot solution.

Actually only the dynamic aspect makes it seem complicated which would also hold true if built with a Pivot as well. In reality the crosstab is much simpler than a Pivot IMHO.

Same code minus the components for the dynamic part.

DECLARE @Actual TABLE (ReportDate date , DatabaseName varchar(10), ReportID int , TotalSum bigint )

INSERT INTO @Actual

SELECT '2016-06-26', 'Test1' , 1, 150

UNION ALL

SELECT '2016-06-27', 'Test2' , 2, 200

UNION ALL

SELECT '2016-06-28', 'Test3' , 3, 150

UNION ALL

SELECT '2016-06-29', 'Test4' , 4, 230

UNION ALL

SELECT '2016-06-30', 'Test5' , 5, 100

--SELECT * FROM @Actual

SELECT ReportDate, 'DatabaseName' AS Colname, cast(DatabaseName AS VARCHAR(500)) AS Value

INTO #TEST

FROM @Actual

UNION ALL

SELECT ReportDate, 'ReportID' AS Colname, cast(ReportID AS VARCHAR(500)) AS Value

FROM @Actual

UNION ALL

SELECT ReportDate, 'TotalSum' AS Colname, cast(TotalSum AS VARCHAR(500)) AS Value

FROM @Actual

SELECT

Colname AS ReportDate,

MAX(CASE WHEN ReportDate = '2016-06-26' THEN Value END) AS '2016-06-26',

MAX(CASE WHEN ReportDate = '2016-06-27' THEN Value END) AS '2016-06-27',

MAX(CASE WHEN ReportDate = '2016-06-28' THEN Value END) AS '2016-06-28',

MAX(CASE WHEN ReportDate = '2016-06-29' THEN Value END) AS '2016-06-29',

MAX(CASE WHEN ReportDate = '2016-06-30' THEN Value END) AS '2016-06-30'

FROM

#Test

GROUP BY Colname

DROP TABLE #TEST

EDIT: Fixed copy and paste mistake in code.

SELECT quote FROM brain WHERE original = 1
0 rows returned

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 8

durga.palepu (6/28/2016)
Yet another good alternative to learn but little complex compared to pivot solution.

It depends. Did you read the article I recommended? Pivot doesn't do so well on "End of row totals" and the like.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Charmer SSChampion Points: 14903 More actions · Answer 9

I am getting different values for DatabaseName, ReportID on same date.

I want to show both values. But pivot choose max value.

Possible to handle this?

Thanks,
Charmer

Umer Akram SSC-Insane Points: 22209 More actions · Answer 10

sample data and desired output will be a lot helpful.

durga.palepu Hall of Fame Points: 3530 More actions · Answer 11

Thanks for the response.

The recommended article is too good to miss.

durga.palepu Hall of Fame Points: 3530 More actions · Answer 12

durga.palepu

Hall of Fame

Points: 3530

June 29, 2016 at 1:17 am

#1887514

Thanks for the updated solution.

Charmer SSChampion Points: 14903 More actions · Answer 13

I am not able to get the sample output

but here is the actual data

/* ACTUAL TABLE */

DECLARE @Actual TABLE (ReportDate date , DatabaseName varchar(10), ReportID int , TotalSum bigint )

INSERT INTO @Actual

SELECT '2016-06-26', 'Test1' , 1, 150

UNION ALL

SELECT '2016-06-27', 'Test2' , 2, 200

UNION ALL

SELECT '2016-06-28', 'Test3' , 3, 150

UNION ALL

SELECT '2016-06-29', 'Test4' , 4, 230

UNION ALL

SELECT '2016-06-30', 'Test5' , 5, 100

UNION ALL

SELECT '2016-06-30', 'Test6' , 6, 500

SELECT * FROM @Actual

The date '2016-06-30' has two different values..

So in this case, how can we show this? We can't have two column names with same date, right?

Thanks,
Charmer

Umer Akram SSC-Insane Points: 22209 More actions · Answer 14

How about this :

/* ACTUAL TABLE */

DECLARE @Actual TABLE (ReportDate date , DatabaseName varchar(10), ReportID int , TotalSum bigint )

INSERT INTO @Actual

SELECT '2016-06-26', 'Test1' , 1, 150

UNION ALL

SELECT '2016-06-27', 'Test2' , 2, 200

UNION ALL

SELECT '2016-06-28', 'Test3' , 3, 150

UNION ALL

SELECT '2016-06-29', 'Test4' , 4, 230

UNION ALL

SELECT '2016-06-30', 'Test5' , 5, 100

UNION ALL

SELECT '2016-06-30', 'Test6' , 10, 150

; WITH cteActual (ReportDate, DatabaseName, ReportID, TotalSum)

AS

(

select a.ReportDate, adj.csvDatabaseName, adj.csvReportID, sum(a.TotalSum) as TotalSum

from @Actual a

Cross Apply

(

select STUFF((Select ', ' + s.DatabaseName

FROM @Actual s

Where s.ReportDate = a.ReportDate

FOR XML PATH(''),TYPE).value('./text()[1]', 'nvarchar(MAX)')

,1,2,'')

,STUFF((Select ', ' + cast(s.ReportID as nvarchar(500))

FROM @Actual s

Where s.ReportDate = a.ReportDate

FOR XML PATH(''),TYPE).value('./text()[1]', 'nvarchar(MAX)')

,1,2,'')

)adj (csvDatabaseName, csvReportID)

Group by a.ReportDate, adj.csvDatabaseName, adj.csvReportID

)

, CTE (ReportDate, colname, Value)

AS

(

select ReportDate, 'Database Name', DatabaseName

from cteActual

union all

select ReportDate, 'ReportID', ReportID

from cteActual

UNION ALL

select ReportDate, 'TotalSum', cast(TotalSum as nvarchar(MAX))

from cteActual

)

select *

from

(

select c.ReportDate, c.colname, c.Value

from cte c

) t1

Pivot

(

max(value) for t1.ReportDate IN ([2016-06-26], [2016-06-27], [2016-06-28],[2016-06-29],[2016-06-30])

) as t2