How to convert verical table into horizontal table

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

General SQL Server Forums

New to SQL Server Programming

How to convert verical table into horizontal table

Author

Topic

supriyasalvi
Starting Member

2 Posts

Posted - 2008-03-04 : 10:14:46

I’ve a vertical table as follows: CMDATA

ID FormID RecordCount FieldName Value
1 525 1204 Name John
2 588 1204 Organization GE
3 525 1257 Name Peter
4 588 1257 Organization Intel
5 525 1272 Name Rita

And a horizontal table as follows: CMFORM

ID FormID RecordCount
1 525 1204
2 525 1257
3 525 1272
4 588 1204
5 588 1257

Where
CMDATA.FormID = CMFORM.FormID and
CMDATA.RecordCount = CMFORM.Record Count

Now I want select records as follows:

RecordCount Name Organization
1204 John GE
1257 Peter Intel
1272 Rita NULL

I’ve written following query for that but it works properly only if all the record count has similar fieldnames.

SELECT CMCF.RecordCount,
CMD1.VALUE AS Name,
CMD2.VALUE AS Organization
FROM CMDATA CMD1
JOIN CMFORMS CMCF ON CMD1.FORMID = CMCF.FORMID AND CMD1.RECORDCount = CMCF.RECORDCount
JOIN CMDATA CMD2 ON CMD2.FORMID = CMCF.FORMID AND CMD2.RECORDCount = CMCF.RECORDCount
WHERE CMD1.FIELDNAME = 'Name'
AND CMD2.FIELDNAME = 'Organization'
AND CMCF.RecordCount in(1204,1257,1272)

Is there any other way to handle this?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-03-04 : 10:29:56

[code]SELECT a.RecordCount,
a.Value AS Name,
b.Value AS Organization
FROM CMDATA a
LEFT OUTER JOIN CMDATA b
ON a.RecordCount = b.RecordCount
AND b.FormID = 588
WHERE a.FormID = 525[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-04 : 10:30:29

[code]SELECT CMFORM.RecordCount,
MAX(CASE WHEN CMDATA.FieldName='Name' THEN CMDATA.[Value] ELSE NULL END) AS [Name],
MAX(CASE WHEN CMDATA.FieldName='Organization' THEN CMDATA.[Value] ELSE NULL END) AS Organization
FROM CMFORM
INNER JOIN CMDATA
ON CMDATA.FormID = CMFORM.FormID and
CMDATA.RecordCount = CMFORM.RecordCount
GROUP BY CMFORM.RecordCount[/code]

also i doubt why you want CMFORM table at all? your o/p can also be achieved by

[code]SELECT RecordCount,
MAX(CASE WHEN FieldName='Name' THEN [Value] ELSE NULL END) AS [Name],
MAX(CASE WHEN FieldName='Organization' THEN [Value] ELSE NULL END) AS Organization
FROM CMDATA
GROUP BY RecordCount[/code]

supriyasalvi
Starting Member

2 Posts

Posted - 2008-03-04 : 10:52:05

Hey visakh16,

It worked...
Thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources