Transpose rows to columns - not a crosstab
-
I have a table of id/value pairs that i need to transform into a "flat" table structure to ease the extraction of this data for statistical purposes. The thing is I do not want to sum or otherwise aggregate the values so the traditional approach of a crosstab query will not work in this case. Also I will have a mix of numeric and char data in the values. So for example, right now I have the following
vID, cID, value
1,1,0
1,2,'test'
2,1,4
2,2,'foo'
And the output I need is the following:
vID,cID(1),cID(2),....cID(n)
1,0,'test'...
2,4,'foo'...
etc.
I have looked at the crosstab examples on this site and while they have been helpful, I still can't seem to figure this one out. Perhaps it is so straightforward it is right under my nose.
Any help or ideas would be greatly appreciated.
-Chris
-
I don't quite get what you want.
Could you expand on your example and desired result, and provide a few rows with "accurate" data?
(I don't see how the example result correlates to the example data..)
/Kenneth
-
If I understand maybe this could be a way...
Given yourTable:
vID, cID, value
1,1,0
1,2,'test'
2,1,4
2,2,'foo'
you can:
SELECT vID,MAX(value1),MAX(value2),MAX(value3)
FROM (
SELECT vID
,(Case WHEN cID=1 THEN value ELSE null END) AS value1
,(Case WHEN cID=2 THEN value ELSE null END) AS value2
,(Case WHEN cID=3 THEN value ELSE null END) AS value3
FROM yourTable
) AS Q1
ORDER BY vID
-
Sorry I forgot to paste ONE LINE (GROUP BY)
If I understand maybe this could be a way...
Given yourTable:
vID, cID, value
1,1,0
1,2,'test'
2,1,4
2,2,'foo'
you can:
SELECT vID,MAX(value1),MAX(value2),MAX(value3)
FROM (
SELECT vID
,(Case WHEN cID=1 THEN value ELSE null END) AS value1
,(Case WHEN cID=2 THEN value ELSE null END) AS value2
,(Case WHEN cID=3 THEN value ELSE null END) AS value3
FROM yourTable
) AS Q1
GROUP BY vID
ORDER BY vID
-
Using case statements won't work unless I can dynamically generate the number of case statements since the table width may vary.
Ken- basically what i want to do is transpose row values to column values like a crosstab, but using the values not an aggregate value. So, for example, currently the values are stored as id/value pairs associated with an id (ID,controlID,value). So each row in the table would be something like the following:
1,1,0
1,2,'test'
2,1,4
2,2,'foo'
This data id represents the control values for both controls 1 and 2 for ID's 1 and 2. In tabular view it would be:
Control(1) Control(2)
ID(1) 1 'test'
ID(2) 4 'foo'
I need to convert the original table to a tabular representation like above. So the final table would be:
1,1,'test
2,4,'foo'
Hope this helps clarify things.
Thanks in advance.
-
tab1 - original data table
tab2 - temp table
tab3 - final output
assuming you have the same number of cID rows for each vID this should work
CREATE PROCEDURE [dbo].[ANewProc]
AS
declare @myVar1 varchar(1000),@myVar2 varchar(1000),@myVar3 varchar(1000),@myVar4 varchar(1000)
drop table tab3
SELECT DISTINCT cID INTO [tab2] FROM [tab1]
Set @myVar1='SELECT DISTINCT tab1.vID'
Set @myVar2= ' INTO tab3 FROM [tab1] '
Set @myVar3= ' WHERE '
SELECT @myVar1=@myVar1 + ', t' + cID + '.myText as cID' + cID FROM [tab2]
SELECT @myVar2=@myVar2 + ', [tab1] t' + cID FROM [tab2]
SELECT @myVar3=@myVar3 + ' tab1.vID=t' + cID + '.vID AND t' + cID + '.cID=' + cID + ' AND ' FROM [tab2]
If Right(@myVar3,4)='AND '
Begin
Set @myVar4=@myVar1 + @myVar2 + Left(@myVar3,Len(@myVar3)-4) + ' ORDER BY tab1.vID '
End
Else
Begin
Set @myVar4=@myVar1 + @myVar2 + @myVar3 + ' ORDER BY tab1.vID '
End
print @myVar4
exec(@myVar4)
drop table tab2
Select * From tab3
GO
-
Oh i forgot - if cID is a numerical field then change the eighth line from
SELECT DISTINCT cID INTO [tab2] FROM [tab1]
to
SELECT DISTINCT RTrim(Cast(cID as char(2))) as cID INTO [tab2] FROM [tab1]
-
Thanks for all the helpful suggestions.
I was able to accomplish what I need by using dynamic sql to generate the required number of self joins - each of which represented a distinct new column header.
-Chris
-
may I ask how u did it (I'm in front of a similar situation). would you mind telling us more on how you accomplished it using dynamic sql
-
Basically what I did was build a sql string with a several sections:
1. Header
2. column list from joins
3. FROM section
4. SELF JOINS from variables I wanted to transpose
5. WHERE clause
Sections 1,3 and 5 were static and 2 & 5 were built up inside a cursor loop. Then I just exec'd all the statement sections together (i.e. exec(sql1+sql2+sql3+sql4+sql5))
Here's what the part of the final statement looked like. Note that each of the LEFT JOIN sections is a Self Join which is referenced in the select list. Hope this helps.
SELECT DISTINCT VisitID,Patients.alias as PatientID,VisitDate, C1.DocumentControlValue as [WMS-R Delay:SMF_SubjNumber],C2.DocumentControlValue as [WMS-R Delay:NUM_IRSscore],C3.DocumentControlValue as [WMS-R Delay:NUM_IRStotal recall score],C4.DocumentControlValue as [WMS-R Delay:SMF_SubjInitials]
FROM PatientVisitCRFs pvc
LEFT JOIN Patients ON pvc.SysPID = Patients.SysPID
INNER JOIN PatientVisits ON Patients.PSID = PatientVisits.PSID
RIGHT JOIN StudyVisits ON pvc.SysVisitID = StudyVisits.SysVisitID
INNER JOIN StoredSMDocuments ON pvc.SysPatCRFID = StoredSMDocuments.SysPatCRFID
INNER JOIN StoredSMDocumentPages sdp ON StoredSMDocuments.SysDocumentID = sdp.SysDocumentID AND StoredSMDocuments.SysPatCRFID = sdp.SysPatCRFID
INNER JOIN StoredSMDocumentControls ON sdp.SysPatCRFID = StoredSMDocumentControls.SysPatCRFID AND sdp.SysDocumentPageID = StoredSMDocumentControls.SysDocumentPageID
INNER JOIN Studies ON StudyVisits.SysStudyID = Studies.SysStudyID
LEFT JOIN (SELECT DISTINCT DocumentControlValue,sysPatCRFID,SysDocumentPageID from StoredSMDocumentControls where sysDocumentControlID =31306254100799) C1 on pvc.SysPatCRFID = C1.SysPatCRFID and sdp.sysDocumentPageID = C1.sysDocumentPageID
LEFT JOIN (SELECT DISTINCT DocumentControlValue,sysPatCRFID,SysDocumentPageID from StoredSMDocumentControls where sysDocumentControlID =31321247303485) C2 on pvc.SysPatCRFID = C2.SysPatCRFID and sdp.sysDocumentPageID = C2.sysDocumentPageID
LEFT JOIN (SELECT DISTINCT DocumentControlValue,sysPatCRFID,SysDocumentPageID from StoredSMDocumentControls where sysDocumentControlID =31321247303485) C3 on pvc.SysPatCRFID = C3.SysPatCRFID and sdp.sysDocumentPageID = C3.sysDocumentPageID
LEFT JOIN (SELECT DISTINCT DocumentControlValue,sysPatCRFID,SysDocumentPageID from StoredSMDocumentControls where sysDocumentControlID =31329620736837) C4 on pvc.SysPatCRFID = C4.SysPatCRFID and sdp.sysDocumentPageID = C4.sysDocumentPageID
WHERE [put where statement here]
-
Another solution to the aforementioned problem:
select distinct cID into #temptable from yourTable
declare @var1 varchar(1000)
set @var1='SELECT vID'
select @var1=@var1+',MAX((Case WHEN cID='+quotename(cID,'''')+' THEN [value] ELSE null END)) AS ['+cID+'] ' from #temptable
exec (@var1+' FROM yourTable AS Q1 GROUP BY vID ORDER BY vID')
drop table #temptable
-
While pivoting can be accomplished in Transact SQL, it's not very pretty, readable, mantainable, scalable or debugable
In short, it's ugly.From the soultion one can see without too much fantasy that the best place (so far) is that, if possible, doing this on the client side.
PIVOT will come in SQL Server 2005 - things will (hopefully) be much easier then.
(however, it's not dynamic, so the "unknown columns" problem may still be a problem)
Lastly, just a note on dynamic SQL - it comes with a prize, make sure you understand the consequences of using dynamic SQL. (...as I'm typing this from "not my computer", Frank has to jump in here with Erland's link)
/Kenneth
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply