March 27, 2002 at 5:11 pm
Is there a way in t-sql to transform data stored in row form to column form. By this i mean the following:
message: type: value
abc123: ID: 2
abc123: matl: iron
abc123: svisor: john
abc124: ID: 3
abc124: matl: steel
abc124: svisor: fred
become
message: ID: Matl: Svisor
Abc123: 2: iron: john
abc124: 3: steel: fred
Rlahav
March 29, 2002 at 2:21 am
For fixed numbers of columns, you can do something like this:
select message,
max(case when type='id' then value end) as 'ID',
max(case when type='matl' then value end) as 'Malt',
max(case when type='svisor' then value end) as 'Svisor'
from tbname
group by message
If you don't know the #col or want to do dynamically, consider RAC.
--
-oj
April 1, 2002 at 12:59 pm
This is called a pivot table which if is unknown number then there is a piece of code I post in scripts you can use as a blue print to deal with. See http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=204
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 1, 2002 at 3:47 pm
Excuse my lack of knowledge but what is "RAC"?
Thanks in advance
quote:
For fixed numbers of columns, you can do something like this:select message,
max(case when type='id' then value end) as 'ID',
max(case when type='matl' then value end) as 'Malt',
max(case when type='svisor' then value end) as 'Svisor'
from tbname
group by message
If you don't know the #col or want to do dynamically, consider RAC.
--
-oj
Rlahav
Rlahav
April 4, 2002 at 5:41 am
This is RAC: http://www.rac4sql.com, I guess ojn is a fan user or maybe the CEO 🙂
By the way, if you retrieve this data in an excel sheet, you can crosstab all you want.
April 4, 2002 at 6:51 am
You can accomplish it doing full joins.
Create this table and enter the following data:
CREATE TABLE [test1] (
[rowid] [int] IDENTITY (1, 1) NOT NULL ,
[student] [int] NOT NULL ,
[Qid] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ans] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_test1] PRIMARY KEY CLUSTERED
(
[rowid]
) ON [PRIMARY]
) ON [PRIMARY]
GO
student,qid,ans
1,Q1,Yes
1,Q2,No
2,Q1,No
2,Q2,No
Run this query to get the results:
SELECT distinct r0.Student,r1.q1,r2.q2 FROM [test1] R0
full join (SELECT student,ans q1 FROM [test1] where qid='Q1') as R1
on r0.student=r1.student
full join (SELECT student,ans q2 FROM [test1] where qid='Q2') as R2
on r0.student=r2.student
April 4, 2002 at 4:11 pm
Silly me, i thought it was a technology (an extension to t-sql)i hadn't heard anything about.
quote:
This is RAC: http://www.rac4sql.com, I guess ojn is a fan user or maybe the CEO 🙂By the way, if you retrieve this data in an excel sheet, you can crosstab all you want.
Rlahav
Rlahav
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply