| Author |
 Topic  |
|
tintin31
Starting Member
USA
14 Posts |
 Posted - 03/02/2001 : 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 |
|
|
Jay99
Constraint Violating Yak Guru
USA
468 Posts |
Posted - 03/02/2001 : 15:54:09
|
You could do this with a simple cursor, but let's see if someone comes up with something better . . .
Jay
|
 |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 03/02/2001 : 16:11:15
|
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 |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 03/02/2001 : 16:32:37
|
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 |
|
|
|
tintin31
Starting Member
USA
14 Posts |
Posted - 03/02/2001 : 18:15:06
|
Thanks RV,
You are a star. I totally forgot about the system tables.
This works  |
|
|
|
kkabbara
Starting Member
1 Posts |
Posted - 12/12/2001 : 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
USA
15559 Posts |
|
|
peter_e
Starting Member
Switzerland
1 Posts |
Posted - 12/17/2001 : 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
USA
1 Posts |
Posted - 01/02/2002 : 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 Posts |
Posted - 10/25/2002 : 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
USA
15559 Posts |
Posted - 10/25/2002 : 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 Posts |
Posted - 01/08/2003 : 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
USA
7423 Posts |
Posted - 01/08/2003 : 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
USA
3 Posts |
Posted - 06/03/2005 : 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
USA
2 Posts |
Posted - 09/22/2006 : 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
USA
2 Posts |
Posted - 09/22/2006 : 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 - 05/15/2007 : 15:48:44
|
| SPAM DELETED |
Edited by - jsmith8858 on 05/15/2007 16:02:18 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 10/21/2008 : 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
India
11 Posts |
Posted - 02/04/2009 : 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
Flowing Fount of Yak Knowledge
India
1693 Posts |
Posted - 02/04/2009 : 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
India
11 Posts |
Posted - 02/04/2009 : 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. |
|
|
|
Topic |
|
|
|
Reverse pivot table
