Stuck with PIVOT
-
Hi,
To simplify the problem, I have JOINed 3 tables together to create a new table with the following output:
I think I should be able to use the PIVOT command to do this, but I can't seem to make any progress. Any input would be appreciated.
-
Thank you for trying to provide a well-formed question. It looks like homework, which is fine, I am happy to help but I need a few things first:
1. DDL to create your table, i.e. CREATE TABLE statement
2. DML to create some test data, i.e. INSERT statements
3. most importantly, the queries you have tried so far to resolve the issue yourself
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Here is how I created the test data:
--DDLCREATE TABLE MainTable_T(
Movie VARCHAR(30),
MainTable_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED
)
CREATE TABLE AttributeType_T(
AttributeType VARCHAR(30),
AttributeType_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED
)
CREATE TABLE AttributeValue_T(
MainTable_ID INT NOT NULL,
AttributeType_ID INT NOT NULL,
AttributeValue VARCHAR(30),
CONSTRAINT PK_AttributeValue_T PRIMARY KEY NONCLUSTERED (MainTable_ID, AttributeType_ID)
)
GO
--DML
INSERT INTO MainTable_T VALUES ('Star Trek')
INSERT INTO MainTable_T VALUES ('Star Wars')
INSERT INTO MainTable_T VALUES ('Battlestar Galactica')
INSERT INTO AttributeType_T VALUES ('Vulcans')
INSERT INTO AttributeType_T VALUES ('Light Sabers')
INSERT INTO AttributeType_T VALUES ('Laser Pistols')
GO
INSERT INTO AttributeValue_T VALUES (1,1,'Y')
INSERT INTO AttributeValue_T VALUES (1,3,'Y')
INSERT INTO AttributeValue_T VALUES (2,2,'Y')
INSERT INTO AttributeValue_T VALUES (2,3,'Y')
INSERT INTO AttributeValue_T VALUES (3,3,'Y')
GO
--See what the data looks like
SELECT m.MainTable_ID AS MovieID, m.Movie, at.AttributeType_ID AS AttributeID, at.AttributeType, av.AttributeValue
INTO Movies_T
FROM MainTable_T m
LEFT OUTER JOIN AttributeValue_T av ON m.MainTable_ID = av.MainTable_ID
INNER JOIN AttributeType_T at ON av.AttributeType_ID = at.AttributeType_ID
GO
SELECT * FROM Movies_T
Here are my feeble attempts when I was using all 3 tables with JOINS. I got so confused that I created the simplified table Movies_T. I have not yet tried anything with this simplified table as I just can't wrap my head around what the PIVOT command is doing.
--Original attemptSELECT m.Movie, avV.AttributeValue AS Vulcans, avP.AttributeValue AS LaserPistols, avS.AttributeValue AS LightSabers
FROM MainTable_T m
LEFT OUTER JOIN AttributeValue_T avV ON m.MainTable_ID = avV.MainTable_ID
LEFT OUTER JOIN AttributeType_T atV ON avV.AttributeType_ID = atV.AttributeType_ID AND atV.AttributeType = 'Vulcans'
LEFT OUTER JOIN AttributeValue_T avP ON m.MainTable_ID = avP.MainTable_ID
LEFT OUTER JOIN AttributeType_T atP ON avP.AttributeType_ID = atP.AttributeType_ID AND atP.AttributeType = 'Laser Pistols'
LEFT OUTER JOIN AttributeValue_T avS ON m.MainTable_ID = avS.MainTable_ID
LEFT OUTER JOIN AttributeType_T atS ON avS.AttributeType_ID = atS.AttributeType_ID AND atS.AttributeType = 'Light Sabers'
--Frist attempt with JOIN
SELECT 'name' AS ID, [1], [2], [3]
FROM
(SELECT m.Movie, m.MainTable_ID, av.AttributeType_ID, at.AttributeType
FROM MainTable_T m
LEFT OUTER JOIN AttributeValue_T av ON m.MainTable_ID = av.MainTable_ID
INNER JOIN AttributeType_T at ON av.AttributeType_ID = at.AttributeType_ID ) AS SourceTable
PIVOT
(
max(at.AttributeType)
FOR av.AttributeType_ID IN ([1], [2], [3])
) AS PivotTable
-
PIVOT is nice but it's really just syntactical-sugar Microsoft added in SQL Server 2005 to implement a classic cross-tab query:
SELECT m.Movie,MAX(CASE WHEN AttributeType = 'Vulcans' THEN 'Y'
ELSE ''
END) AS Vulcans
FROM MainTable_T m
JOIN AttributeValue_T av ON m.MainTable_ID = av.MainTable_ID
JOIN AttributeType_T at ON av.AttributeType_ID = at.AttributeType_ID
GROUP BY Movie;
You can easily see what's happening in the above query and add the other two columns. I like this form of the query better than using PIVOT and it has the advantage of usually performing better than PIVOT as well.
If you want to read more Jeff Moden deconstructs and demonstrates the topic quite well: Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
opc.three
Your query is spot on! Best of all I can understand what it is doing. Thanks for the tip. I will also read the link you supplied and see what I can learn.
Actually, this is huge. For too long at my work they have been using the db as a fancy Excel spreadsheet. My attempts at NF3 have always been rebuffed because of my inability to report the data in a spreadsheet view. This will change all that!
Thanks again! 🙂
-
Since the OP asked for a PIVOT example here's the code. I created some indexes to try and make sure it was a level playing field. If you run either of these queries they have identical query plans (attached below). The real problem with both of these approaches is when the attribute types are dynamic such as might occur when using an Entity Attribute Value (EAV) table structure. (Another good reason not to use EAV!)
Create the test tables and indexes:
CREATE TABLE MainTable_T(
Movie VARCHAR(30),
MainTable_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED
)
GO
CREATE TABLE AttributeType_T(
AttributeType VARCHAR(30),
AttributeType_ID INT IDENTITY (1,1) PRIMARY KEY CLUSTERED
)
GO
CREATE TABLE AttributeValue_T(
MainTable_ID INT NOT NULL,
AttributeType_ID INT NOT NULL,
AttributeValue VARCHAR(30),
CONSTRAINT PK_AttributeValue_T PRIMARY KEY NONCLUSTERED (MainTable_ID, AttributeType_ID)
)
GO
INSERT INTO MainTable_T VALUES ('Star Trek')
INSERT INTO MainTable_T VALUES ('Star Wars')
INSERT INTO MainTable_T VALUES ('Battlestar Galactica')
INSERT INTO AttributeType_T VALUES ('Vulcans')
INSERT INTO AttributeType_T VALUES ('Light Sabers')
INSERT INTO AttributeType_T VALUES ('Laser Pistols')
GO
INSERT INTO AttributeValue_T VALUES (1,1,'Y')
INSERT INTO AttributeValue_T VALUES (1,3,'Y')
INSERT INTO AttributeValue_T VALUES (2,2,'Y')
INSERT INTO AttributeValue_T VALUES (2,3,'Y')
INSERT INTO AttributeValue_T VALUES (3,3,'Y')
GO
CREATE NONCLUSTERED INDEX [IX_AttributeValue_Cover] ON [dbo].[AttributeValue_T]
(
[MainTable_ID] ASC,
[AttributeType_ID] ASC,
[AttributeValue] ASC
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_MainTable_T_Movie_MainTableID] ON [dbo].[MainTable_T]
(
[MainTable_ID] ASC,
[Movie] ASC
) ON [PRIMARY]
GO
PIVOT query
SELECT
Result.Movie
,ISNULL(Result.[Vulcans],'') AS [Vulcans]
,ISNULL(Result.[Light Sabers],'') AS [LightSabers]
,ISNULL(Result.[Laser Pistols],'') AS [LaserPistols]
FROM
(
SELECT
m.Movie
,at.AttributeType
,av.AttributeValue
FROM
MainTable_T m
INNER JOIN
AttributeValue_T av
ON m.MainTable_ID = av.MainTable_ID
INNER JOIN
AttributeType_T at
ON av.AttributeType_ID = at.AttributeType_ID
WHERE
m.MainTable_ID > 0
) M
PIVOT
(MAX(AttributeValue) FOR AttributeType IN ([Vulcans],[Light Sabers],[Laser Pistols]))
AS Result
opc.three's code without PIVOT
SELECT
m.Movie
,MAX(CASE WHEN AttributeType = 'Vulcans' THEN 'Y'
ELSE ''
END) AS Vulcans
,MAX(CASE WHEN AttributeType = 'Light Sabers' THEN 'Y'
ELSE ''
END) AS LightSabers
,MAX(CASE WHEN AttributeType = 'Laser Pistols' THEN 'Y'
ELSE ''
END) AS LaserPistols
FROM
MainTable_T m
INNER JOIN
AttributeValue_T av
ON m.MainTable_ID = av.MainTable_ID
INNER JOIN
AttributeType_T at
ON av.AttributeType_ID = at.AttributeType_ID
WHERE
m.MainTable_ID > 0
GROUP BY Movie
The output:
MovieVulcansLightSabersLaserPistols
Battlestar GalacticaY
Star TrekYY
Star WarsYY
-
All the above methods would get you the required data. This is just an extension to them.
Incase you have more than three attribute Types which may change, then you can use the Dynamic Version of the Cross Tab as follows :
Declare @sql Varchar(MAX)
Select @sql = 'Select m.Movie, ' + STUFF((Select DISTINCT ',MAX(Case When at.AttributeType = ' + CHAR(39) + at.AttributeType + CHAR(39) + ' Then av.AttributeValue Else '''' End) As [' + at.AttributeType + ']' From MainTable_T m LEFT OUTER JOIN AttributeValue_T av ON m.MainTable_ID = av.MainTable_ID INNER JOIN AttributeType_T at ON av.AttributeType_ID = at.AttributeType_ID For XML PATH('')),1,1,'')
Select @sql = @sql + ' From MainTable_T m LEFT OUTER JOIN AttributeValue_T av ON m.MainTable_ID = av.MainTable_ID INNER JOIN AttributeType_T at ON av.AttributeType_ID = at.AttributeType_ID Group By m.Movie '
Execute(@sql)
Hope that helps. 🙂
-
Thanks for all the responses! While knowledge is a good thing, this has certainly complicated my design plans. But it is certainly better to know ahead of time so thanks again. 🙂
-
vinu512 (1/28/2013)
All the above methods would get you the required data. This is just an extension to them.Incase you have more than three attribute Types which may change, then you can use the Dynamic Version of the Cross Tab as follows :
Declare @sql Varchar(MAX)
Select @sql = 'Select m.Movie, ' + STUFF((Select DISTINCT ',MAX(Case When at.AttributeType = ' + CHAR(39) + at.AttributeType + CHAR(39) + ' Then av.AttributeValue Else '''' End) As [' + at.AttributeType + ']' From MainTable_T m LEFT OUTER JOIN AttributeValue_T av ON m.MainTable_ID = av.MainTable_ID INNER JOIN AttributeType_T at ON av.AttributeType_ID = at.AttributeType_ID For XML PATH('')),1,1,'')
Select @sql = @sql + ' From MainTable_T m LEFT OUTER JOIN AttributeValue_T av ON m.MainTable_ID = av.MainTable_ID INNER JOIN AttributeType_T at ON av.AttributeType_ID = at.AttributeType_ID Group By m.Movie '
Execute(@sql)
Hope that helps. 🙂
Thanks for that....very helpful.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply