SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Reverse pivot table
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

tintin31
Starting Member

USA
14 Posts

Posted - 03/02/2001 :  14:19:48  Show Profile  Reply with Quote
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

Jay99
Constraint Violating Yak Guru

USA
468 Posts

Posted - 03/02/2001 :  15:54:09  Show Profile  Reply with Quote
You could do this with a simple cursor, but let's see if someone comes up with something better . . .

Jay

Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 03/02/2001 :  16:11:15  Show Profile  Visit robvolk's Homepage  Reply with Quote
SELECT ID, 'FirstName' AS Question, FirstName AS Answer
UNION
SELECT ID, 'LastName' AS Question, LastName AS Answer
UNION
SELECT ID, 'SSN' AS Question, SSN AS Answer


Non-character columns would need to be Convert()'ed, but this will cover everything:

SELECT ID, 'FirstName' AS Question, Convert(varchar(50),FirstName) AS Answer
UNION
SELECT ID, 'LastName' AS Question, Convert(varchar(50),LastName) AS Answer
UNION
SELECT ID, 'SSN' AS Question, Convert(varchar(50),SSN) AS Answer


Give me a minute, I think I might have an idea that can do the whole table automatically.

Edited by - robvolk on 03/02/2001 16:13:25
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 03/02/2001 :  16:32:37  Show Profile  Visit robvolk's Homepage  Reply with Quote
And here it is:

DECLARE @sql varchar(2000), @table varchar(50)
SELECT @sql = '', @table = 'myTable'
SELECT @sql = @sql + 'SELECT ID, ''' + column_name + ''' AS Question,
Convert(varchar(50),' + column_name + ') AS Answer FROM ' + table_name + ' UNION '
FROM information_schema.columns
WHERE table_name=@table AND column_name<>'ID'
SELECT @sql = Left(@sql,Len(@sql)-5)
EXEC (@sql)


You can put this in a procedure and pass the table name, instead of declaring it like I did here.

Edited by - robvolk on 03/02/2001 16:48:37
Go to Top of Page

tintin31
Starting Member

USA
14 Posts

Posted - 03/02/2001 :  18:15:06  Show Profile  Reply with Quote
Thanks RV,
You are a star. I totally forgot about the system tables.

This works
Go to Top of Page

kkabbara
Starting Member

1 Posts

Posted - 12/12/2001 :  16:39:04  Show Profile  Reply with Quote
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..

Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 12/12/2001 :  17:20:37  Show Profile  Visit robvolk's Homepage  Reply with 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

Go to Top of Page

peter_e
Starting Member

Switzerland
1 Posts

Posted - 12/17/2001 :  13:02:03  Show Profile  Reply with Quote
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
Go to Top of Page

cdturner
Starting Member

USA
1 Posts

Posted - 01/02/2002 :  04:40:00  Show Profile  Reply with Quote
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
Go to Top of Page

natiej
Starting Member

1 Posts

Posted - 10/25/2002 :  11:49:16  Show Profile  Reply with Quote
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





Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 10/25/2002 :  12:01:31  Show Profile  Visit robvolk's Homepage  Reply with Quote
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'"

Go to Top of Page

eoro7
Starting Member

1 Posts

Posted - 01/08/2003 :  06:54:09  Show Profile  Reply with Quote
I need more than 8000 chars in my query to do the crosstab, how can I do that?

Thanks
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 01/08/2003 :  19:53:19  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

LinuxGold
Starting Member

USA
3 Posts

Posted - 06/03/2005 :  11:34:43  Show Profile  Send LinuxGold an AOL message  Reply with Quote
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.
Go to Top of Page

mwhite007
Starting Member

USA
2 Posts

Posted - 09/22/2006 :  18:59:25  Show Profile  Visit mwhite007's Homepage  Reply with Quote
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.
Go to Top of Page

mwhite007
Starting Member

USA
2 Posts

Posted - 09/22/2006 :  22:03:27  Show Profile  Visit mwhite007's Homepage  Reply with Quote
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.
Go to Top of Page

paulmelba
Starting Member

8 Posts

Posted - 05/15/2007 :  15:48:44  Show Profile  Visit paulmelba's Homepage  Click to see paulmelba's MSN Messenger address  Reply with Quote
SPAM DELETED

Edited by - jsmith8858 on 05/15/2007 16:02:18
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/21/2008 :  00:12:27  Show Profile  Reply with Quote
quote:
Originally posted by brendans

Hi,

Try creating a pivot using the view itself:

(spam deleted)

Cheers!


solution after 1 year?
Go to Top of Page

basawareddy
Starting Member

India
11 Posts

Posted - 02/04/2009 :  02:09:02  Show Profile  Reply with Quote
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
Go to Top of Page

bklr
Flowing Fount of Yak Knowledge

India
1693 Posts

Posted - 02/04/2009 :  02:25:46  Show Profile  Reply with Quote
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
Go to Top of Page

basawareddy
Starting Member

India
11 Posts

Posted - 02/04/2009 :  04:00:05  Show Profile  Reply with Quote
Hi Bklr,

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

Thanks alot.

Regards,
Basawareddy.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000