Reverse pivot table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Reverse pivot table

Author

Topic

tintin31
Starting Member

14 Posts

Posted - 2001-03-02 : 14:19:48

Hello All,

I am trying to do the reverse of a pivot table.
Example

given the following record:

ID Firstname Lastname SSN
-- --------- -------- ----
1 Bob Sims 123-45-6789

I wish to create rows:

ID Question Answer
-- --------- -----------
1 Firstname Bob
1 Lastname Sims
1 SSN 123-45-6789

Essentially I am trying to make columns into rows. Is this possible.

All responses appreciated

kkabbara
Starting Member

1 Post

Posted - 2001-12-12 : 16:39:04

Hi
I am also having a similar issue.

I have a table that looks like this:
ID Label Results
=== ============ ========================
1 q1 option1
1 q2 option2
1 q3 option3

etc.

I would to grab the table and change it to the following:

ID = 1
q1 q2 q3
====== ========= =========
option1 option2 option3
etc.

Any thoughts?
Thanks a Bill..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-12-12 : 17:20:37

That would be a regular cross-tab. These articles should do the trick:

http://www.sqlteam.com/item.asp?ItemID=2955
http://www.sqlteam.com/item.asp?ItemID=5741

peter_e
Starting Member

1 Post

Posted - 2001-12-17 : 13:02:03

quote:

Hi
I am also having a similar issue.

I have a table that looks like this:

ID UseCase FieldName Data
----------- ------------------- ------------------- ---------------
1 create-Order Customer-Ref 333300000016
1 create-Order TotalAmount 350.00
1 create-Order TotalPositions 27
2 get-Offer TotalAmount 350.00
2 get-Offer Currency CHF

etc

...and I would to grab the table and change it to the following:

for UseCase create-Order

Customer-Ref TotalAmount TotalPositions
--------------- ------------------- -------------------
333300000016 350.00 27

for UseCase get-Offer

TotalAmount Currency
------------------- -------------------
350.00 CHF

etc.

Any thoughts for a solution?

Thanks a lot for your help..

Edited by - peter_e on 12/17/2001 13:22:51

cdturner
Starting Member

1 Post

Posted - 2002-01-02 : 04:40:00

At first glance these solutions seem to use the SUM function which only works on numeric values, how do you deal with non-numeric values in a cross-tab without resulting in separate rows for each entry (by removing the SUM)

should be ...
[Person ID] [Home Phone] [Work Phone]
12000      545-2344      434-2342

but turns out as
[Person ID] [Home Phone] [Work Phone]
12000      545-2344      NULL
12000      NULL         434-2342

or alternatively if it can't be done as the table is generated, is there anyway to recombine the multiple rows for a particular [person id] into a single row ?

Thanks

Chris Turner

natiej
Starting Member

1 Post

Posted - 2002-10-25 : 11:49:16

Wow! thanks for the great code for the stored procedure to create a pivot crosstab query. This is the first time I have used a stored procedure... Took me a few minutes to figure out how to get the code to work but now it works great.

I have one question . . . how do I pass in a WHERE or a HAVING statement to the SQL statement that I feed into the stored procedure?

This works:

MySQL = "EXECUTE niftycrosstab 'SELECT Drawing, PartNumber,PartName FROM CrossTab GROUP BY Drawing, PartNumber,PartName','sum(MyQty)','DashNumber','CrossTab'"

This does not:

MySQL = "EXECUTE niftycrosstab 'SELECT Drawing, PartNumber,PartName FROM CrossTab GROUP BY Drawing HAVING Drawing = ‘101401’, PartNumber,PartName','sum(MyQty)','DashNumber','CrossTab'"

Any ideas?

quote:

That would be a regular cross-tab. These articles should do the trick:

http://www.sqlteam.com/item.asp?ItemID=2955
http://www.sqlteam.com/item.asp?ItemID=5741

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-25 : 12:01:31

The syntax was a little off, this should work:

MySQL = "EXECUTE niftycrosstab 'SELECT Drawing, PartNumber,PartName FROM CrossTab GROUP BY Drawing, PartNumber,PartName HAVING Drawing = ‘101401’','sum(MyQty)','DashNumber','CrossTab'"

And you don't need to use HAVING in this case because you're not using a summary function on it, you can use it in the WHERE clause:

MySQL = "EXECUTE niftycrosstab 'SELECT Drawing, PartNumber,PartName FROM CrossTab WHERE Drawing = ‘101401’ GROUP BY Drawing, PartNumber,PartName', 'sum(MyQty)','DashNumber','CrossTab'"

eoro7
Starting Member

1 Post

Posted - 2003-01-08 : 06:54:09

I need more than 8000 chars in my query to do the crosstab, how can I do that?

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-01-08 : 19:53:19

Does it have to be dynamic, or are the columns always the same?

If they are, you can just write the SQL out (or have something similiar to Rob's stored proc generate it for you) and leave the view/stored proc as is, without using dynamic SQL.

If not ..... maybe create a view with as much of the SQL as you can, and instead of passing a long SQL statement to the stored proc, pass in the view name??

- Jeff

LinuxGold
Starting Member

3 Posts

Posted - 2005-06-03 : 11:34:43

SELECT
Associates.LastName + ', ' + Associates.FirstName as name,
QA.Batch,
QAErrors.ErrorTypeID,
count(ErrorTypeID) as total
FROM Associates,QA
JOIN Batch ON Batch.QAID=QA.ID
JOIN QAErrors ON QAErrors.ID=Batch.QEID
WHERE KeyDate
BETWEEN '2005-05-01'
AND '2005-05-07'
AND Associates.ID=QA.OperatorID
GROUP BY Associates.LastName,Associates.FirstName,Associates.ID,QA.Batch,ErrorTypeID
ORDER BY Associates.LastName,Associates.FirstName,QA.Batch;

I would like it to list as:
associate name batch error 1 error 2 error 3
Baker, Kathy 412458004 2
Baldwin, Alec 412457900 1 5 1
Baldwin, Alec 412458005 1
Baldwin, Alec 425404135 1
Hamm, Scott 412457889 1 10
Hamm, Scott 412457903 1 1

How can I use crosstab to accomplish this?

Power to people, Linux is here.

mwhite007
Starting Member

2 Posts

Posted - 2006-09-22 : 18:59:25

I think this is similar to what I want to do.
on each row of one of my timecard tables I have TimeCardID, WeekEnding, SSN, JobNum, OT, DT, Reg, Misc
the last four columns are the result of a calculation of timecard punches.
But the Oracle system needs the data in separate rows with an Expenditure_Type column which would be either OT,DT,Reg,Misc.
I don't need to aggregate anything, just transform the one row into four rows.
for bonus points, only make a row if the column value is greater than zero so you would end up with 1 to 4 rows.

mwhite007
Starting Member

2 Posts

Posted - 2006-09-22 : 22:03:27

Ok, so I dug out my SQL Cookbook and derived this:
select SSN,JONum,TaskNum,ExpendTypes.Expenditure_Type,WeekEnding,
case ExpendTypes.ExpendTypeCode
when 'RegHours' then RegHours
when 'OTHours' then OTHours
when 'DTHours' then DTHours
when 'MiscHours' then MiscHours
end as QTYHours
from (
SELECT WeekEnding,SSN,JONum,TaskNum,RegHours,OTHours,DTHours,MiscHours
FROM dbo.vwTCTransfer
) EmpHours,ExpendTypes
where ExpendTypes.ExpendTypeCode in('RegHours','OTHours','DTHours','MiscHours')
order by WeekEnding,SSN

But I can't save it as a view... only a stored procedure. Not sure how to use it.

paulmelba
Starting Member

8 Posts

Posted - 2007-05-15 : 15:48:44

SPAM DELETED

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-21 : 00:12:27

quote:
Originally posted by brendans

Hi,

Try creating a pivot using the view itself:

(spam deleted)

Cheers!

solution after 1 year?

basawareddy
Starting Member

11 Posts

Posted - 2009-02-04 : 02:09:02

Hi Friends,

I need one help to display data in following format.

I have data in a table as shown below.

No Date Code Result
1 1/1/2009 PE 11.02
1 1/12009 AE .8
1 1/12009 ALC 11
1 1/12009 CB 33
1 1/12009 EE 55
1 1/12009 TT 9
1 1/12009 HH 97
1 1/12009 Hr .834

I need in below format.

No Date PE AE ALC CB EE TT HH HR
1 1/1/2009 11.02 .8 11 33 55 9 97 .834

Thanks
Basawareddy

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-04 : 02:25:46

declare @tab table( No int, Date datetime, Code varchar(32), Result decimal(18,2))
insert into @tab select
1,'1/1/2009','PE',11.02 union all select
1,'1/1/2009','AE',.8 union all select
1,'1/1/2009','ALC',11 union all select
1,'1/1/2009','CB',33 union all select
1,'1/1/2009','EE',55 union all select
1,'1/1/2009','TT',9 union all select
1,'1/1/2009','HH',97 union all select
1,'1/1/2009','Hr',.834

select no,date,max(case when code = 'pe' then result end) as 'PE',
max(case when code = 'AE' then result end) as 'AE',
max(case when code = 'ALC' then result end) as 'ALC',
max(case when code = 'CB' then result end) as 'CB',
max(case when code = 'EE' then result end) as 'EE',
max(case when code = 'TT' then result end) as 'TT',
max(case when code = 'HH' then result end) as 'HH',
max(case when code = 'Hr' then result end) as 'Hr'
from @tab
group by no,date

basawareddy
Starting Member

11 Posts

Posted - 2009-02-04 : 04:00:05

Hi Bklr,

its Great, i got exact result.
I was missing Max, becoz of that i was getting null values.

Thanks alot.

Regards,
Basawareddy.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-04 : 04:04:30

ur welcome

try to post as new post don't post ur query in other's post

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-04 : 09:06:53

quote:
Originally posted by basawareddy

Hi Bklr,

its Great, i got exact result.
I was missing Max, becoz of that i was getting null values.

Thanks alot.

Regards,
Basawareddy.

Hi Basawareddy,
In future please post questions as a new thread rather than hijacking old thread. This will increase visibility and you'll get solutions quicker.

ssatjap1
Starting Member

4 Posts

Posted - 2011-11-28 : 10:40:16

Hello, I'm very new to SQL, and I am trying to figure out how to pivot the following table:

date ID Y/N
2/1 1 Y
2/2 2 N
2/3 3 NULL
etc..

I want to keep the dates as rows, make the ID as columns, and the Y/N as the value.

I have a very large data set, and I want to use the ID numbers as the column names.

For example:

Date 1 2 3
2/1 Y NULL NULL
2/2 NULL N NULL
2/3 NULL NULL NULL

etc.

I would appreciate anyone's help on this matter.

Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-28 : 10:48:20

quote:
Originally posted by ssatjap1

Hello, I'm very new to SQL, and I am trying to figure out how to pivot the following table:

date ID Y/N
2/1 1 Y
2/2 2 N
2/3 3 NULL
etc..

I want to keep the dates as rows, make the ID as columns, and the Y/N as the value.

I have a very large data set, and I want to use the ID numbers as the column names.

For example:

Date 1 2 3
2/1 Y NULL NULL
2/2 NULL N NULL
2/3 NULL NULL NULL

etc.

I would appreciate anyone's help on this matter.

Thank you.

already replied in new thread

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources