CODEWITHSUNDEEP
sql-server-2008
InfoLearner
InfoLearner

Reputation: 15598

How do I get this result using SQL Server 2008 query?

I have two tables:

Product -< Category (1 product can have many categories)

Product table has three columns:

ProductId, Name, Type
P1,Alpha Product,ClassA
P2,Beta Product,ClassB

Category table has four columns:

CategoryId, Name, Value, ProductId
C1, Category1, 0.5, P1
C2, Category2, 0.75, P1
C3, Category1, 0.25, P2
C4, Category2, 0.15, P2

What I want to achieve is a single row per product with each category as a column and value as a row e.g.:

ProductId, Category1, Category2
P1, 0.5, 0.75
P2, 0.25, 0.15

Any help is appreciated

Upvotes: 0

Views: 69

Answers (1)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

Modified to demonstrate both static and dynamic methods 

CREATE  TABLE #t
    (
      CategoryID CHAR(2)
    , NAME VARCHAR(15)
    , VALUE DECIMAL(3, 2)
    , ProductID CHAR(2)
    )
INSERT  INTO #t
        ( CategoryID
        , NAME
        , VALUE
        , ProductID 
        )
        SELECT  'C1'
              , 'Category1'
              , 0.5
              , 'P1'
        UNION
        SELECT  'C2'
              , 'Category2'
              , 0.75
              , 'P1'
        UNION
        SELECT  'C3'
              , 'Category1'
              , 0.25
              , 'P2'
        UNION
        SELECT  'C4'
              , 'Category2'
              , 0.15
              , 'P2'
SELECT  ProductID
      , Category1
      , Category2
FROM    ( SELECT    NAME
                  , VALUE
                  , ProductID
          FROM      #t t
        ) AS Src PIVOT ( AVG(VALUE) FOR NAME IN ( [Category1], [Category2] ) ) AS p


 --dynamically

--build your columns list 
DECLARE @columns VARCHAR(MAX)
SELECT  @columns = STUFF(( SELECT DISTINCT
                                    ',' + NAME
                           FROM     #t t
                           ORDER BY ',' + NAME
                         FOR
                           XML PATH('')
                         ), 1, 1, '')    

--build your sql statement 
DECLARE @sql VARCHAR(MAX)
SET @sql = REPLACE('SELECT  ProductID
      , |@columns|
FROM    ( SELECT    NAME
                  , VALUE
                  , ProductID
          FROM      #t t
        ) AS Src PIVOT ( AVG(VALUE) FOR NAME IN ( |@columns| ) ) AS p', '|@columns|', @columns)

 EXEC(@sql)

Upvotes: 4

Related Questions