Pivot table with UNION - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Pivot table with UNION

Author

Topic

BlackDog
Starting Member

18 Posts

Posted - 2002-11-11 : 16:50:19

I have a situation where I need to take data from one table and pivot it to be inserted into another table. The structure for the table to be pivoted is:

CREATE TABLE [dbo].[SubAgency] (
[SubAgencyID] [int] IDENTITY (1, 1) NOT NULL ,
[AgencyID] [int] NOT NULL ,
[SubAgencyName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SubAgencyTypeID] [int] NOT NULL ,
[Address1] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateID] [int] NOT NULL ,
[Zip] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[URL] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[County] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [int] NULL ,
[ORI] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comment] [nvarchar] (2300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreateDate] [datetime] NOT NULL ,
[CreateUser] [int] NOT NULL ,
[IsActive] [bit] NOT NULL
)

and the structure for the table that is going to accept the data is:

CREATE TABLE [dbo].[DataValues] (
[DataValueID] [bigint] IDENTITY (1, 1) NOT NULL ,
[QuestionID] [int] NOT NULL ,
[FieldID] [int] NOT NULL ,
[Data] [sql_variant] NULL ,
[FieldLookupValueID] [int] NULL ,
[DataFieldLookupValue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CaseID] [int] NOT NULL ,
[Holdback] [bit] NOT NULL ,
[LargeMultTable] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LargeMultID] [int] NULL ,
[LargeMultInstID] [int] NULL ,
[SmallMultTable] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SmallMultID] [int] NULL ,
[SmallMultInstID] [int] NULL ,
[LastUpdate] [timestamp] NULL
)

The value that is in the column of the SubAgency table needs to be inserted into the Data column of the DataValues table. Right now, I’m using the following UNION query to get the data in the correct format:

SELECT Fields.QuestionID AS QuestionID,
3 AS FieldID,
SubAgencyName AS Data,
NULL AS FieldLookupValueID,
NULL AS DataFieldLookupValue,
inserted.CaseID AS CaseID,
inserted.AgencyHoldback AS Holdback
FROM SubAgency sag JOIN inserted ON sag.SubAgencyID = inserted.SubAgencyID JOIN Fields ON Fields.FieldID = 3

UNION
SELECT Fields.QuestionID AS QuestionID,
4 AS FieldID,
Address1 AS Data,
NULL AS FieldLookupValueID,
NULL AS DataFieldLookupValue,
inserted.CaseID AS CaseID,
inserted.AgencyHoldback AS Holdback
FROM SubAgency sag JOIN inserted ON sag.SubAgencyID = inserted.SubAgencyID JOIN Fields ON Fields.FieldID = 4

UNION
SELECT Fields.QuestionID AS QuestionID,
136 AS FieldID,
Address2 AS Data,
NULL AS FieldLookupValueID,
NULL AS DataFieldLookupValue,
inserted.CaseID AS CaseID,
inserted.AgencyHoldback AS Holdback
FROM SubAgency sag JOIN inserted ON sag.SubAgencyID = inserted.SubAgencyID JOIN Fields ON Fields.FieldID = 136

I’m wondering if this is the best way to go, or if there is another way to get the data from the SubAgency table into the correct format. As you can see from the use of the inserted table, I’m performing this insert in a trigger.

Thanks for your help.

Ryan

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-12 : 06:47:31

I don't understand the UNION? Why not use an IN in your ON?

SELECT Fields.QuestionID AS QuestionID,
Fields.FieldID AS FieldID,
SubAgencyName AS Data,
NULL AS FieldLookupValueID,
NULL AS DataFieldLookupValue,
inserted.CaseID AS CaseID,
inserted.AgencyHoldback AS Holdback
FROM SubAgency sag JOIN inserted ON sag.SubAgencyID = inserted.SubAgencyID JOIN Fields ON Fields.FieldID in (3,4,136)

Jay White
{0}

BlackDog
Starting Member

18 Posts

Posted - 2002-11-12 : 10:37:26

Look closely at the three select statements. Each of them have a different value for FieldID and each of them are selecting a different column which is then aliased as 'Data'.

I only showed three of the select statements that are unioned together, but there is one select statement for just about each column in the SubAgency table, so I can get a corresponding row inserted into the DataValues table. Each select statement selects a different column from SubAgency, depending on the value for Fields.FieldID.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-12 : 13:18:29

I am not sure if I am totally understand your needs 100%, but it sounds like this could be one of the many uses for a cartesian product -- but in an unusual way. Bear with me on this one:

First, you need a table listing all field ID's. Create one if you don't have it. Lets call this table FIELDS.

Now, for your SELECT statement, instead of multiple UNIONS all over the place, you use the Cartesian product of the data and the FIELDS table to enumerate your data tables as many times as there are fields. Then, you use a CASE statement to pick out the right field depending on the fieldID value.

For example, suppose the data is being inputted as ("Input") :

ID Name Address ZIP
---------------------------
1 Jeff 1 Elm St 01222

And you have a table of fields ("Fields")

FieldID Field
----- -----
1 Name
2 Address
3 ZIP

And you want to store it as ("Data"):

ID FieldID Value
-- ------- ------
1 1 Jeff
1 2 1 Elm St
1 3 01222

If this is the case, you need to spread out the data into multiple rows. Instead of doing UNIONS, use a cross join of the Fields table witht the Input table. Do that with this statement:

INSERT INTO Data
SELECT
Input.ID, Fields.FieldID, CASE Fields.FieldID WHEN 1 THEN Input.Name WHEN 2 THEN Input.Address WHEN 3 THEN Input.Zip END as Value
FROM Fields CROSS JOIN Input

This is much easier than multiple UNIONS. I hope this is what you are looking for or at least helps you out.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-12 : 13:23:30

by the way -- this is actually what I call "Un-Pivoting" ... when you pivot, you turn multiple rows into 1 row and spread it out among columns (less rows, more columns); when you "un-pivot" you spread out multiple columns as multiple rows (more rows, less columns).

In other words, pivotting turns a table from long and skinny to short and wide; un-pivoting goes from short and wide to long and skinny.

In my opinion, the best way to "un-pivot" is to do a cartesian with a table of fields as opposed to numerious UNIONS. A good query processor will only evaluatate the data once and then spread it out among the columns; for multiple UNIONS, the data need to be evaluated over and over.

BlackDog
Starting Member

18 Posts

Posted - 2002-11-12 : 16:49:01

Thanks for the tip. So far, it seems to be working great, with an increase in speed of about 8x over the UNION's. Below is what I ended up with. If you see anything else I can improve upon, please let me know.


SELECT * FROM (
	SELECT Fields.QuestionID AS QuestionID, 
		Fields.FieldID AS FieldID, 
		CASE Fields.FieldID WHEN 3 THEN SubAgencyName WHEN 4 THEN Address1 WHEN 5 THEN City
		WHEN 6 THEN County WHEN 7 THEN CAST(StateID AS varchar(10)) WHEN 8 THEN Zip 
		WHEN 136 THEN Address2 WHEN 137 THEN URL
		WHEN 9 THEN CAST(Country AS varchar(10))
		WHEN 138 THEN Phone WHEN 139 THEN Fax WHEN 141 Then Comment WHEN 10 THEN ORI END AS Data, 
		CASE Fields.FieldID WHEN 7 THEN StateID WHEN 9 THEN Country ELSE NULL END AS FieldLookupValueID,
		CASE Fields.FieldID WHEN 7 THEN (SELECT LookupName FROM FieldLookupValues WHERE FieldLookupValueID = StateID) 
		WHEN 9 THEN (SELECT LookupName FROM FieldLookupValues WHERE FieldLookupValueID = Country) ELSE NULL END AS DataFieldLookupValue,
		cases.CaseID AS CaseID, 
		cases.AgencyHoldback AS Holdback 
	FROM SubAgency sag JOIN cases ON sag.SubAgencyID = cases.SubAgencyID CROSS JOIN Fields
	WHERE Cases.CaseID = 2
		AND Fields.FieldID IN (3,4,5,6,7,8,9,136,137,138,139,141,10)
	) AS SI
WHERE SI.Data IS NOT NULL

Thanks again.

Ryan

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-12 : 17:19:12

Glad it worked, and much shorter, too! Usually shorter = easier to understand = faster = much better!

The only thing I can think of to improve your query might be to do all of your lookups as JOINS (either OUTER or INNER depending on your data) in the main query, instead of sub queries. I have found that, in general, I try to avoid subqueries as they can be inefficient. Again, though, I am not sure about this so try whatever works best and is easiest to understand and debug going forward.

-Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources