pivot multiple columns and transpose to same row sql server

Question

Post reply

pivot multiple columns and transpose to same row sql server

sugaz007

Old Hand

Points: 310

March 5, 2021 at 6:38 pm

Go to Answer

#3853967

I have a table like below:

CREATE TABLE #Test
(Category_ID INT, [Type] VARCHAR(2),Student_ID INT, SHARE INT, ROW1 INT)

insert into #Test values (70788 ,'A', 308, 100 ,1)
insert into #Test values (70788 ,'MA', 20821, 50 ,1)
insert into #Test values (70788 ,'MA', 1328 , 50 ,2)

insert into #Test values (55555 ,'A', 108 , 50 ,1)
insert into #Test values (55555 ,'A', 4948 , 50 ,2)
insert into #Test values (55555 ,'MA', 9484 , 100 ,1)

select * from #Test

Original

I want the output to be as below:

output

The Category_ID should be the same, AND ROW1 should be the same. [TYPE] should

be USED TO PIVOT the value FOR Student_ID & Share. WHERE NO record IS available it should be NULL

Please help. Thanks

Steve Collins

SSCrazy

Points: 2173

March 5, 2021 at 6:57 pm

Answer

#3853972

It seems you're looking to include the ROW1 column in the GROUP BY and ORDER BY clauses but not in the SELECT list. Otherwise, it seems to be a standard PIVOT or CROSSTAB query which could be accomplished with a conditional aggregation approach something like this

drop table if exists #Test;
go
create table #Test(
  Category_IDINT,
  [Type]VARCHAR(2),
  Student_IDINT,
  SHAREINT,
  ROW1INT);

insert into #Test values
(70788 ,'A', 308, 100 ,1),
(70788 ,'MA', 20821, 50 ,1),
(70788 ,'MA', 1328 , 50 ,2),
(55555 ,'A', 108 , 50 ,1),
(55555 ,'A', 4948 , 50 ,2),
(55555 ,'MA', 9484 , 100 ,1);

select Category_ID,
       max(case when [Type]='A' then Student_ID else null end) Student_ID_A,
       max(case when [Type]='A' then SHARE else null end) SHARE_A,
       max(case when [Type]='MA' then Student_ID else null end) Student_ID_MA,
       max(case when [Type]='MA' then SHARE else null end) SHARE_MA
from #Test
group by Category_ID, ROW1
order by Category_ID desc, ROW1;

Category_IDStudent_ID_ASHARE_AStudent_ID_MASHARE_MA
707883081002082150
70788NULLNULL132850
55555108509484100
55555494850NULLNULL

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 4 posts - 1 through 3 (of 3 total)

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

sugaz007 Old Hand Points: 310 More actions · Answer 1

sugaz007

Old Hand

Points: 310

March 5, 2021 at 7:13 pm

#3853975

Thanks a lot. That works

ScottPletcher SSC Guru Points: 101120 More actions · Answer 2

I was coding a solution for this on the other Sql Srv forum where you posted -- and then deleted -- this q.

Here is my answer: the ISNULL(...) stuff is just to prevent the warning msg, since a lot of people hate getting that msg.

SELECT
    Category_ID,
    NULLIF(MAX(CASE WHEN Type = 'A' THEN Student_ID ELSE -999999 END), -999999) AS StudentID_A,
    NULLIF(MAX(CASE WHEN Type = 'A' THEN SHARE ELSE -999999 END), -999999) AS SHARE_A,
    NULLIF(MAX(CASE WHEN Type = 'MA' THEN Student_ID ELSE -999999 END), -999999) AS StudentID_MA,
    NULLIF(MAX(CASE WHEN Type = 'MA' THEN SHARE ELSE -999999 END), -999999) AS SHARE_MA
FROM #Test t
GROUP BY Category_ID, ROW1
ORDER BY Category_ID, ROW1

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".