May 12, 2003 at 8:45 am
Hello All,
To sum it up: I am trying to condense the values which are in 6 different recordsets into a single recordset so graphing etc can be done with it.
I am trying to create a view that will select the following situation and want to know how to best construct it. I could select from another view but that is generally not recommended. Here is my situation.
I have a table which contains data which resembles a spreadsheet. I have attributes which represent the rows and columns which represent - well columns. Each record in the table corresponds to a unique row-column.
Each "spreadsheet" has an instance id.
I am trying to build a view that will merge the data that I need into a single record. I am trying to return columnids (1,2,3,6,7) values into a single recordset for each attribute.
Example - Take the following
AttributeID ColumnID Data
12 1 x
12 2 y
12 3 z
Merge into single recordset
AttributeID 1Data 2Data 3Data
12 x y z
Thanks,
Drew
May 12, 2003 at 9:19 am
You can use case statements to denormalise data:
select distinct AttributeId,
case ColumnID when 1 then Data else 0 as col1
case ColumnID when 2 then Data else 0 as col2
case ColumnID when 3 then Data else 0 as col3
from <table_name>
etc....
You can build up a series of these from different tables usin the UNION operator but make sure that each select statement uses exactly the same column names.
Jeremy
May 12, 2003 at 9:36 am
Thanks Jeremy,
It puts me on the right track, but how do I get the data into one
recordset?
I now have the following:
Select Distinct AttributeID,
CASE dbo.QIMS_FORMS_INSTANCE_DATA.ColumnID WHEN 1 THEN
dbo.QIMS_FORMS_INSTANCE_DATA.Data End AS Target,
CASE dbo.QIMS_FORMS_INSTANCE_DATA.ColumnID WHEN 2 THEN
dbo.QIMS_FORMS_INSTANCE_DATA.Data End AS LowerLimit,
CASE dbo.QIMS_FORMS_INSTANCE_DATA.ColumnID WHEN 3 THEN
dbo.QIMS_FORMS_INSTANCE_DATA.Data End AS UpperLimit
From dbo.QIMS_FORMS_INSTANCE_DATA
WHERE AttributeID = 1
May 13, 2003 at 1:18 am
You can use the UNION operator to concatentate the results from different tables:
Select Distinct AttributeID,
CASE dbo.QIMS_FORMS_INSTANCE_DATA.ColumnID WHEN 1 THEN
dbo.QIMS_FORMS_INSTANCE_DATA.Data End AS Target,
CASE dbo.QIMS_FORMS_INSTANCE_DATA.ColumnID WHEN 2 THEN
dbo.QIMS_FORMS_INSTANCE_DATA.Data End AS LowerLimit,
CASE dbo.QIMS_FORMS_INSTANCE_DATA.ColumnID WHEN 3 THEN
dbo.QIMS_FORMS_INSTANCE_DATA.Data End AS UpperLimit
From dbo.QIMS_FORMS_INSTANCE_DATA
WHERE AttributeID = 1
UNION
Select Distinct AttributeID,
CASE dbo.QIMS_FORMS_INSTANCE_DATA.ColumnID WHEN 1 THEN
dbo.QIMS_FORMS_INSTANCE_DATA.Data End AS Target,
CASE dbo.QIMS_FORMS_INSTANCE_DATA.ColumnID WHEN 2 THEN
dbo.QIMS_FORMS_INSTANCE_DATA.Data End AS LowerLimit,
CASE dbo.QIMS_FORMS_INSTANCE_DATA.ColumnID WHEN 3 THEN
dbo.QIMS_FORMS_INSTANCE_DATA.Data End AS UpperLimit
From dbo.QIMS_FORMS_INSTANCE_DATA
WHERE AttributeID = 2
UNION
Select Distinct AttributeID,
CASE dbo.QIMS_FORMS_INSTANCE_DATA.ColumnID WHEN 1 THEN
dbo.QIMS_FORMS_INSTANCE_DATA.Data End AS Target,
CASE dbo.QIMS_FORMS_INSTANCE_DATA.ColumnID WHEN 2 THEN
dbo.QIMS_FORMS_INSTANCE_DATA.Data End AS LowerLimit,
CASE dbo.QIMS_FORMS_INSTANCE_DATA.ColumnID WHEN 3 THEN
dbo.QIMS_FORMS_INSTANCE_DATA.Data End AS UpperLimit
From dbo.QIMS_FORMS_INSTANCE_DATA
WHERE AttributeID = 3
I have used the same query with different attributeID values but you could just as easily create a query which comes from another table. For each query, the columns have to be the same so in the above example, all the queries need to produce the same 4 columns: AttributeID; Target; LowerLimit; UpperLimit.
Hope this is clear.
Jeremy
May 13, 2003 at 6:40 am
I wasn't doing a good job of being very clear. Sorry 🙁
I am looking at trying to put the data into a single RECORD not RECORD SET.
My mistake. I am trying to merge/get into a single record the data for
each columnid for that attribute.
AttributeID ColumnID Data
12 1 x
12 2 y
12 3 z
Merge into single RECORD
AttributeID 1Data 2Data 3Data
12 x y z
Thanks,
Drew
May 14, 2003 at 3:01 am
SELECT AttributeID,
MAX(CASE WHEN ColumnID=1 THEN Data ELSE '' END) as '1Data',
MAX(CASE WHEN ColumnID=2 THEN Data ELSE '' END) as '2Data',
MAX(CASE WHEN ColumnID=3 THEN Data ELSE '' END) as '3Data',
MAX(CASE WHEN ColumnID=6 THEN Data ELSE '' END) as '6Data',
MAX(CASE WHEN ColumnID=7 THEN Data ELSE '' END) as '7Data'
FROM table
GROUP BY AttributeID
Far away is close at hand in the images of elsewhere.
Anon.
May 14, 2003 at 7:36 am
THANKS DAVID!!!
That is exactly what I needed.
One thing I definitely need to bone up on is grouping etc.
Thanks to Jeremy as well!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply