Pivot and Unpivot

Question

Pivot and Unpivot

SSCrazy

Points: 2756

April 1, 2019 at 11:37 am

I'm thinking need to do unpivot and pivot to get below result, is there anyway better then i thought :)..Any help appreciated

Thank you in advance.

CREATE TABLE #TEMP
(
    ID VARCHAR(100),
    DATE DATETIME,
    CATEGORY VARCHAR(3),
    AMOUNT MONEY
)

insert into #TEMP values ('224A','1/1/2012', 'ABC', 1000.00)
insert into #TEMP values ('224A','2/1/2012', 'ABC', 500.00)
insert into #TEMP values ('234A','2/1/2012', 'GHI', 800.00)
insert into #TEMP values ('234A','2/10/2012', 'DEF', 700.00)
insert into #TEMP values ('229B','3/1/2012', 'ABC', 1100.00)

Select * FROM #TEMP

--OUTPUT---

224A  '1/1/2012'   '2/1/2012'  10000
224A   1000         500
234A  '2/1/2012'   '2/10/2012' 
234A   800           700
229B  '03/1/2012'
229B   1100

This topic was modified 5 years, 1 month ago by koti.raavi.
This topic was modified 5 years, 1 month ago by koti.raavi. Reason: Spelling correction
This topic was modified 5 years, 1 month ago by administrator.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

koti.raavi wrote:

I’m thinking need to do unpivot and pivot to get below result, is there anyway better then i thought :)..Any help appreciated Thank you in advance.
CREATE TABLE #TEMP
(
ID VARCHAR(100),
DATE DATETIME,
CATEGORY VARCHAR(3),
AMOUNT MONEY
)
insert into #TEMP values ('224A','1/1/2012', 'ABC', 1000.00)
insert into #TEMP values ('224A','2/1/2012', 'ABC', 500.00)
insert into #TEMP values ('234A','2/1/2012', 'GHI', 800.00)
insert into #TEMP values ('234A','2/10/2012', 'DEF', 700.00)
insert into #TEMP values ('229B','3/1/2012', 'ABC', 1100.00)
Select * FROM #TEMP
--OUTPUT---
224A '1/1/2012' '2/1/2012' 10000
224A 1000 500
234A '2/1/2012' '2/10/2012'
234A 800 700
229B '03/1/2012'
229B 1100

Unfortunately, it's hard to understand what you need from what you posted. You seem to be trying to have columns with different data types in them. That's not possible to do. You could return all in a single row and format them as two lines in the presentation layer.

Here's an example of what you can do.

WITH CTE AS(
    SELECT *, 
        (ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE) + 1)/2 grouper, 
        (ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE) + 1)%2 rownum
    FROM #TEMP
)
SELECT ID,
    MAX( CASE WHEN rownum = 0 THEN DATE   END) AS Date1,
    MAX( CASE WHEN rownum = 0 THEN AMOUNT END) AS Amount1,
    MAX( CASE WHEN rownum = 1 THEN DATE   END) AS Date2,
    MAX( CASE WHEN rownum = 1 THEN AMOUNT END) AS Amount2
FROM CTE
GROUP BY ID, grouper;

This reply was modified 5 years ago by Dave Convery.
This reply was modified 5 years ago by Dave Convery.
This reply was modified 5 years ago by Dave Convery.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

koti.raavi SSCrazy Points: 2756 More actions · Answer 2

I see it, i have added extra number in first line, which is incorrect. Below is the corrected output.

one ID can have multiple rows

224A ‘1/1/2012’ ‘2/1/2012’
224A 1000 500
224A ABC ABC
234A ‘2/1/2012’ ‘2/10/2012'
234A 800 700
234A GHI DEF
229B ’03/1/2012’
229B 1100
229B ABC

drew.allen SSC Guru Points: 76948 More actions · Answer 3

A database is not a spreadsheet. You can't do a simple transpose like you can in Excel.

Databases are strongly typed, but Excel is not.
- Database fields all have to have the same data type, but Excel columns can have mixed data types.
- You are trying to mix data types, which is not allowed.
SQL Server enforces 1^st Normal Form. Excel doesn't enforce normalization at all.
- You are trying to have rows with different numbers of columns, which is not allowed.

Drew

This reply was modified 5 years, 1 month ago by drew.allen. Reason: Fixing formatting issues
This reply was modified 5 years, 1 month ago by drew.allen. Reason: Trying to fix formatting issues. The bullet items aren't being retained

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ScottPletcher SSC Guru Points: 100950 More actions · Answer 4

For clarification, a SQL column does not have to be all the same type: SQL has a type of sql_variant that is designed to handle different data types in the same column. Thus, if you really needed it, you could mix standard data types in the same SQL table column.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

drew.allen SSC Guru Points: 76948 More actions · Answer 5

ScottPletcher wrote:

For clarification, a SQL column does not have to be all the same type: SQL has a type of sql_variant that is designed to handle different data types in the same column. Thus, if you really needed it, you could mix standard data types in the same SQL table column.

I would argue that they ARE all the same data type: sql_variant. The fact that you have to explicitly convert sql_variant values to the "base data type value before it can participate in operations..." indicates that it isn't stored as it's base data type.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

koti.raavi SSCrazy Points: 2756 More actions · Answer 6

I can use cast/convert to change data type of column while doing pivot/unpivot. Can above solution is feasible?

drew.allen SSC Guru Points: 76948 More actions · Answer 7

koti.raavi wrote:

I can use cast/convert to change data type of column while doing pivot/unpivot. Can above solution is feasible?

It's a BAD idea and you really need to have a compelling reason to do so. Barring such a compelling reason, it is usually best to do this in your presentation layer instead of in the database layer.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

femi.olaniyan SSC Enthusiast Points: 151 More actions · Answer 8

Here is a solution. You would need to adapt it as you have more instances of each ID, but it should be pretty straightforward to change. Otherwise you can use dynamic sql to create the pivot column list.

;

with cte as

(

select a.*

, 'col' + cast(ROW_NUMBER() over(PARTITION by id order by [date] asc) as varchar(3)) [lbl]

from #temp a

)

, cte_2

as

(

select p.*

from

(

select a.ID , cast(a.DATE as varchar(100)) [type] , a.lbl ,1 [ord]

from cte a

) c

pivot

(

max([type])

For lbl in (col1, col2)

) p

union

select p.*

from

(

select a.ID , cast(a.AMOUNT as varchar(100)) [type] , a.lbl , 2 [ord]

from cte a

) c

pivot

(

max([type])

For lbl in (col1, col2)

) p

union

select p.*

from

(

select a.ID , cast(a.CATEGORY as varchar(100)) [type] , a.lbl , 3 [ord]

from cte a

) c

pivot

(

max([type])

For lbl in (col1, col2)

) p

)

select a.ID , isnull(a.col1, '') col1 , isnull(a.col2 , '') col2

from cte_2 a

order by a.ID , a.ord asc;

This reply was modified 5 years, 1 month ago by femi.olaniyan. Reason: tried to change the formatting