PIVOT on VIEW for "special data"
-
Hi Guys,
I'm in need of a solution that takes data from a primary table and also values from a separate "specialised data" table for each record. For example, I have a item "Stock1" which has the following specific set of attributes:
SpecialField1
SpecialField2
Next, "Stock2" has the following specific set of attributes:
SpecialField1
SpecialField2
SpecialField3
and finally, "Stock3" has the following specific set of attributes:
SpecialField3
SpecialField4
All these "SpecialFieldX" records are defined in a reference table and are able to be associated (only once) to any stock. My code below creates and populates the table necessary:
CREATE TABLE #ReferenceCodes(
[ReferenceGroupCode] [varchar](20) NOT NULL,
[ReferenceCode] [varchar](50) NOT NULL,
[ReferenceName] [varchar](50) NOT NULL,
[Translation] [varchar](100) NULL
) ON [PRIMARY]
GO
INSERT INTO #ReferenceCodes
(ReferenceGroupCode,ReferenceCode,ReferenceName,Translation)
SELECT'SPECIAL_DATA','SpecialField1','Stores special field 1','E'
UNION
SELECT'SPECIAL_DATA','SpecialField2','Stores special field 2','E'
UNION
SELECT'SPECIAL_DATA','SpecialField3','Stores special field 3','F'
UNION
SELECT'SPECIAL_DATA','SpecialField4','Stores special field 4','D'
CREATE TABLE #StockSpecialisedData(
[StockCode] [varchar](20) NOT NULL,
[AttributeName] [varchar](40) NOT NULL,
[AttributeClass] [varchar](30) NOT NULL,
[AttributeValue] [sql_variant] NOT NULL
) ON [PRIMARY]
CREATE TABLE #Stocks (
[StockCode] [varchar](20) NOT NULL
,[StockName] [varchar](50) NOT NULL
)
INSERT INTO #Stocks
(StockCode, StockName)
SELECT'Stock1','Stock 1'
UNION
SELECT'Stock2','Stock 2'
UNION
SELECT'Stock3','Stock 3'
UNION
SELECT'Stock4','Stock 4'
INSERT INTO #StockSpecialisedData
(StockCode,AttributeName,AttributeClass,AttributeValue)
SELECT'Stock1','SpecialField1','GROUP1','2012-03-01'
UNION
SELECT'Stock1','SpecialField2','GROUP1','1000'
UNION
SELECT'Stock2','SpecialField1','GROUP1','2013-01-18'
UNION
SELECT'Stock2','SpecialField2','GROUP1','0.256'
UNION
SELECT'Stock3','SpecialField3','GROUP1','330'
UNION
SELECT'Stock3','SpecialField4','GROUP2','1'
UNION
SELECT'Stock4','SpecialField3','GROUP1','365'
UNION
SELECT'Stock4','SpecialField4','GROUP2','0'
DROP TABLE #Stocks
DROP TABLE #ReferenceCodes
DROP TABLE #StockSpecialisedData
What I would like to be able to do is create a view that pivots the "Specialised Attributes" and returns all the stock-level data, as well as all the specialised data. From my limited knowledge, I'd assume it would need to SELECT from #ReferenceCodes, then LEFT JOIN onto #StockSpecialisedData as I'd like to have all columns even if there's no relating row in #StockSpecialisedData.
Is this possible using a view?
As always, thanks in advance!
Kevin.
For all your clubs - Our Clubs.
Try out our new site today and see how it can help your club! -
Not exactly sure what you want here, but try this
SELECT s.StockCode,s.StockName,
MAX(CASE WHEN AttributeName='SpecialField1' THEN AttributeClass END) AS AttributeClass1,
MAX(CASE WHEN AttributeName='SpecialField1' THEN AttributeValue END) AS AttributeValue1,
MAX(CASE WHEN AttributeName='SpecialField2' THEN AttributeClass END) AS AttributeClass2,
MAX(CASE WHEN AttributeName='SpecialField2' THEN AttributeValue END) AS AttributeValue2,
MAX(CASE WHEN AttributeName='SpecialField3' THEN AttributeClass END) AS AttributeClass3,
MAX(CASE WHEN AttributeName='SpecialField3' THEN AttributeValue END) AS AttributeValue3,
MAX(CASE WHEN AttributeName='SpecialField4' THEN AttributeClass END) AS AttributeClass4,
MAX(CASE WHEN AttributeName='SpecialField4' THEN AttributeValue END) AS AttributeValue4
FROM #Stocks s
LEFT OUTER JOIN #StockSpecialisedData d ON d.StockCode = s.StockCode
GROUP BY s.StockCode,s.StockName
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi SSCommitted,
Thanks for your post. Unfortunately, I won't know how many attributes there will be when I'm looking for a stock. Ideally I'd be looking for something like as follows:
StockCode, StockName, [SpecialField1],[SpecialField2],[SpecialField3],[SpecialFieldn]...
without having to code for each specific field in turn.
I know it can be done using dynamic SQL, but I wanted it as a View so that we'd be able to use it instead of referencing the table directly (and having to write custom code everytime!)
Thanks again!
For all your clubs - Our Clubs.
Try out our new site today and see how it can help your club! -
If you don't know how many attribute there are in advance then you will have to use dynamic SQL - not possible for a view.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply