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
April 1, 2019 at 1:23 pm
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;
April 1, 2019 at 1:45 pm
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
April 1, 2019 at 4:40 pm
A database is not a spreadsheet. You can't do a simple transpose like you can in Excel.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 1, 2019 at 8:13 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 1, 2019 at 10:37 pm
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
April 2, 2019 at 6:08 am
I can use cast/convert to change data type of column while doing pivot/unpivot. Can above solution is feasible?
April 2, 2019 at 2:40 pm
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
April 3, 2019 at 2:43 pm
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;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply