Need help with Pivot multiple columns in a single select

Question

Post reply

Need help with Pivot multiple columns in a single select

rash3554

SSCommitted

Points: 1984
More actions
November 12, 2015 at 4:24 am

#322501

I have a table with school details whose columns are like below
Create table Test(schoolid int, ColumnID int, Value varchar(100))
if columnid=1 then it is Start Date
if columnid=2 then it is enddate
if columnid=3 then it is dollarspent
Test table has about 10 million records
Insert into test Values(1000,1,'2001-01-01')
Insert into test Values(1000,1,'2002-01-01')
Insert into test Values(1000,1,'2003-01-01')
Insert into test Values(1000,2,'2001-12-31')
Insert into test Values(1000,2,'2002-09-01')
Insert into test Values(1000,2,'2003-10-01')
Insert into test Values(1000,3,'2001.01')
Insert into test Values(1000,3,'10000.99')
Insert into test Values(2000,1,'2001-01-01')
Insert into test Values(2000,1,'2002-01-01')
Insert into test Values(2000,1,'2003-01-01')
Insert into test Values(2000,2,'2001-12-31')
Insert into test Values(2000,2,'2002-09-01')
Insert into test Values(2000,2,'2003-10-01')
Insert into test Values(2000,3,'2001.01')
Insert into test Values(2000,3,'10000.99')
Insert into test Values(2000,3,'553.22')
I have a reference table that keeps track of kind of school in terms of funding (private, public, semi-public,semi-private)
and kind of school in terms of areas of study (Eng-engineering,Arts,Music)
Create table RefInfo(schoolid int, ColumnID int, Value varchar(100))
if columnid=1 then it is funding
if columnid=2 then it is typeofschool
Table has about 25000 records
Insert into RefInfo Values(1000,1,'PRI')
Insert into RefInfo Values(2000,1,'PUB')
Insert into RefInfo Values(3000,1,'SEMI-PUB')
Insert into RefInfo Values(4000,1,'SEMI-PRI')
Insert into RefInfo Values(1000,2,'ENG')
Insert into RefInfo Values(2000,2,'MUSIC')
Insert into RefInfo Values(3000,2,'ARTS')
Insert into RefInfo Values(4000,2,'ENG')
I want to select schoolid,startdate,enddate,dollarspent,TypeOfSchool into a work table. I am not sure what's the best way to do this. I first thought of using Pivot for each column but not sure of implications of time taken to execute those queries.
Are there any better ways of doing this?
Thanks
Rash

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: 183706 More actions · Answer 1

Use CROSS TABS[/url] with the MAX() function, probably with a row number to avoid losing rows.

I'm really not sure what's your expected output based on that sample data and explanation.

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

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

Here are a couple of examples:

DECLARE @RefInfo TABLE (schoolid int, ColumnID int, Value varchar(100))

INSERT INTO @RefInfo VALUES (1000,1,'PRI'),(2000,1,'PUB'),(3000,1,'SEMI-PUB'),(4000,1,'SEMI-PRI'),(1000,2,'ENG'),(2000,2,'MUSIC'),(3000,2,'ARTS'),(4000,2,'ENG')

--USING CROSS TABS

SELECT

schoolid,

MAX(CASE WHEN columnID = 1 THEN value END) AS funding,

MAX(CASE WHEN columnID = 2 THEN value END) AS typeOfSchool

FROM

@RefInfo

GROUP BY

schoolid

--USING PIVOT

SELECT

schoolid,

[1] AS funding,

[2] AS typeOfSchool

FROM

@RefInfo

PIVOT (MAX(value) FOR ColumnID IN ([1], [2])) pvt

If you can be more specific about what you need I can come up with a complete solution. That being said if you are not familiar with these techniques it's worth giving them a try for practice.

EDIT: fixed typo, had columnid = 1 twice...copy and paste will kill you every time 😉

SELECT quote FROM brain WHERE original = 1
0 rows returned

rash3554 SSCommitted Points: 1984 More actions · Answer 3

I completely messed up the table.. I am so sorry..

Create table Test(schoolid int, ColumnID int, Value varchar(100), insertedDate Date)

Inserteddate - date when it was collected

Insert into test values(1000,1,'2001-01-01','2005-12-31 00:00:00')

Insert into test values(1000,1,'2002-01-01','2006-12-31 00:00:00')

Insert into test values(1000,1,'2003-01-01','2007-12-31 00:00:00')

Insert into test values(1000,2,'2001-12-31','2005-12-31 00:00:00')

Insert into test values(1000,2,'2002-09-01','2006-12-31 00:00:00')

Insert into test values(1000,2,'2003-10-01','2007-12-31 00:00:00')

Insert into test values(1000,3,'2001.01','2005-12-31 00:00:00')

Insert into test values(1000,3,'10000.99','2006-12-31 00:00:00')

Insert into test values(2000,1,'2001-01-01','2006-12-31 00:00:00')

Insert into test values(2000,1,'2002-01-01','2007-12-31 00:00:00')

Insert into test values(2000,1,'2003-01-01','2008-12-31 00:00:00')

Insert into test values(2000,2,'2001-12-31','2006-12-31 00:00:00')

Insert into test values(2000,2,'2002-09-01','2007-12-31 00:00:00')

Insert into test values(2000,2,'2003-10-01','2008-12-31 00:00:00')

Insert into test values(2000,3,'2001.01','2006-12-31 00:00:00')

Insert into test values(2000,3,'10000.99','2007-12-31 00:00:00')

Insert into test values(2000,3,'553.22','2008-12-31 00:00:00')

Here's what my output should be

select Companyid,startdate,enddate,inserteddate,dollarspent,typeofschool,funding

--to be printed for each school

1000,'2001-01-01','2001-12-31','2001.01','2005-12-31 00:00:00','PRI','ENG'

1000,'2002-01-01','2002-09-01','10000.99','2006-12-31 00:00:00','PRI','ENG'

1000,'2003-01-01','2003-10-01',NULL,'2007-12-31 00:00:00','PRI','ENG'

2000,'2001-01-01','2001-12-31','2001.01','2006-12-31 00:00:00','PUB','MUSIC'

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Please, check your sample data. It won't run.

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

rash3554 SSCommitted Points: 1984 More actions · Answer 5

rash3554

SSCommitted

Points: 1984

November 12, 2015 at 10:49 am

#1839457

oops..I updated my scripts

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

Ok, try something like this:

DECLARE @RefInfo TABLE (schoolid int, ColumnID int, Value varchar(100))

DECLARE @test-2TABLE (schoolid int, ColumnID int, Value varchar(100), insertedDate Date)

INSERT INTO @RefInfo VALUES (1000,1,'PRI'),(2000,1,'PUB'),(3000,1,'SEMI-PUB'),(4000,1,'SEMI-PRI'),(1000,2,'ENG'),(2000,2,'MUSIC'),(3000,2,'ARTS'),(4000,2,'ENG')

INSERT INTO @test-2 VALUES

(1000,1,'2001-01-01','2005-12-31 00:00:00'),

(1000,1,'2002-01-01','2006-12-31 00:00:00'),

(1000,1,'2003-01-01','2007-12-31 00:00:00'),

(1000,2,'2001-12-31','2005-12-31 00:00:00'),

(1000,2,'2002-09-01','2006-12-31 00:00:00'),

(1000,2,'2003-10-01','2007-12-31 00:00:00'),

(1000,3,'2001.01','2005-12-31 00:00:00'),

(1000,3,'10000.99','2006-12-31 00:00:00'),

(2000,1,'2001-01-01','2006-12-31 00:00:00'),

(2000,1,'2002-01-01','2007-12-31 00:00:00'),

(2000,1,'2003-01-01','2008-12-31 00:00:00'),

(2000,2,'2001-12-31','2006-12-31 00:00:00'),

(2000,2,'2002-09-01','2007-12-31 00:00:00'),

(2000,2,'2003-10-01','2008-12-31 00:00:00'),

(2000,3,'2001.01','2006-12-31 00:00:00'),

(2000,3,'10000.99','2007-12-31 00:00:00'),

(2000,3,'553.22','2008-12-31 00:00:00')

SELECT

t.schoolid,

t.Num,

MAX(CASE WHEN t.columnID = 1 THEN t.value END) AS StartDate,

MAX(CASE WHEN t.columnID = 2 THEN t.value END) AS EndDate,

MAX(CASE WHEN t.columnID = 3 THEN t.value END) AS DollarSpent,

MAX(CASE WHEN r.columnID = 2 THEN r.value END) AS typeOfSchool,

MAX(CASE WHEN r.columnID = 1 THEN r.value END) AS funding

FROM

(SELECT schoolid, ColumnID, Value, insertedDate, ROW_NUMBER() OVER (PARTITION BY schoolid, columnid ORDER BY value ASC) AS Num

FROM @test-2

) t

JOIN @RefInfo r ON t.schoolid = r.schoolid

GROUP BY

t.schoolid,

t.Num

FYI - That is a horrible way of storing your data.

SELECT quote FROM brain WHERE original = 1
0 rows returned

rash3554 SSCommitted Points: 1984 More actions · Answer 7

wow! never thought of that approach. It took me a day :-(..and the only solution I thought was creating separate temp tables for each ColumnID with primary key as SchoolID and then Join them.

So I have to now figure which has better performance, temp tables or using CASE statements. This table has about 10 million records i.e. about 60,000 SchoolIDs with different year data. Any thoughts on that??

Thanks a lot

P.S.

This is how I get data from vendor. I need to create reports using this..

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

rash3554 (11/14/2015)
wow! never thought of that approach. It took me a day :-(..and the only solution I thought was creating separate temp tables for each ColumnID with primary key as SchoolID and then Join them.
So I have to now figure which has better performance, temp tables or using CASE statements. This table has about 10 million records i.e. about 60,000 SchoolIDs with different year data. Any thoughts on that??
Thanks a lot
P.S.
This is how I get data from vendor. I need to create reports using this..

Yeah... since you're going to hit the whole table, it's going to be "slow" no matter what. Some thoughtful indexing may help with sorting but you're still going to hit the whole table because there's virtually no way to index by date because of the bad design that allows dates and dollar amounts to be mixed in the same column.

There's also no way to guarantee when the dollars were spent because the type 3 rows have no date on them and you cannot rely on storage order of rows in a table for this type of thing. Except for a grand total over the entire period for a given school, the data is pretty much useless for anything else. I'd introduce the vendor to the concept of providing reasonable data.

--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)