none
Help with Query (kind of PIVOT but not really...)

    Question

  • Hi,

    I am trying to find a solution to a complex query.

    Basically I have 2 tables like this:

    T1
    (AttrID int,   -- Identifier of the value attribute
    Name varchar(50)   -- Value Attribute name
    );

    T2
    (T2ID int,   -- Identifier of the value
    ClientID int,   -- Identifier of the client
    AttrID int,   -- Value Attribute ID
    T2Value float   -- Value for given attribute
    );

    Now I want to build a query that will have as many columns as I have different value attributes.

    I could have a query like this:

    SELECT ClientID,
         AttrID,
         MAX([1]) AS 'Attr1',
         MAX([2]) AS 'Attr2',
         MAX([3]) AS 'Attr3'
    FROM
       (SELECT ClientID, T1ID,
          (CASE WHEN AttrID=1 THEN T2Value ELSE NULL END) AS '1',
          (CASE WHEN AttrID=2 THEN T2Value ELSE NULL END) AS '2',
          (CASE WHEN AttrID=3 THEN T2Value ELSE NULL END) AS '3'
       FROM T2) Source
    GROUP BY ClientID, AttrID

    But as you see as soon as I get new records in T1, I have to modify the query to match the new "structure". Not very friendly and efficient.

    Can you think of something else to build this query?

    I am running SQL2008.


    • Edited by pauldube Tuesday, March 06, 2012 9:09 PM
    Tuesday, March 06, 2012 9:03 PM

Answers

  • Try

    USE tempdb 
    
    CREATE TABLE T1
    (AttrID INT,   -- Identifier of the value attribute
    name VARCHAR(50)   -- Value Attribute name
    );
    INSERT INTO T1 VALUES (1,'Width'),(2,'Length'), (3, 'Density'), (4, 'Velocity'), (5, 'Mass')
    
    CREATE TABLE T2
    (T2ID INT,   -- Identifier of the value
    ClientID INT,   -- Identifier of the client
    AttrID INT,   -- Value Attribute ID
    T2Value FLOAT   -- Value for given attribute
    );
    
    INSERT INTO T2 VALUES (1,1,1,100),(2,1,2,200),(3,1,3,0.5),(4,1,4,70),(5,1,5,100)
    
    DECLARE @SQL NVARCHAR(MAX), @Cols NVARCHAR(MAX)
    
    SELECT @Cols = STUFF((SELECT ', ' + QUOTENAME([name]) FROM T1 ORDER BY [name] FOR XML PATH('')),1,2,'')
    
    SET @SQL = 'SELECT * FROM (SELECT T2.ClientID, T2.T2Value, T1.Name as AttrName 
    from T2 INNER JOIN T1 ON T2.AttrID = T1.AttrID) src
    PIVOT (max(T2Value) FOR AttrName IN (' + @Cols + ')) pvt'
    
    EXECUTE (@SQL)
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by pauldube Wednesday, March 07, 2012 2:59 PM
    Wednesday, March 07, 2012 2:23 PM
  • Try in the @SQL definition:

    SET @SQL = 'SELECT * FROM (SELECT T2.ClientID,

    CASE WHEN T2.T2Value IS NULL then T2.T2Text ELSE CAST(T2Value as VARCHAR(50)) END as T2Value,

    T1.Name as AttrName from T2 INNER JOIN T1 ON T2.AttrID = T1.AttrID) src PIVOT (max(T2Value) FOR AttrName IN (' + @Cols + ')) pvt'



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi NModerator Wednesday, March 07, 2012 3:07 PM
    • Marked as answer by pauldube Wednesday, March 07, 2012 3:11 PM
    Wednesday, March 07, 2012 3:07 PM

All replies

  • You may need to use dynamic pivot.

    This link has examples on how to use dynamic pivot.

    http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Tuesday, March 06, 2012 9:16 PM
  • See also an example of dynamic case based pivot in this thread

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/726cc85c-7985-45a6-98d7-72544c5746ab


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, March 06, 2012 9:35 PM
  • I am lost with those examples. May be it's too early in the morning.

    Can you provide code with my example?

    Wednesday, March 07, 2012 12:43 PM
  • Try

    USE tempdb 
    
    CREATE TABLE T1
    (AttrID INT,   -- Identifier of the value attribute
    name VARCHAR(50)   -- Value Attribute name
    );
    INSERT INTO T1 VALUES (1,'Width'),(2,'Length'), (3, 'Density'), (4, 'Velocity'), (5, 'Mass')
    
    CREATE TABLE T2
    (T2ID INT,   -- Identifier of the value
    ClientID INT,   -- Identifier of the client
    AttrID INT,   -- Value Attribute ID
    T2Value FLOAT   -- Value for given attribute
    );
    
    INSERT INTO T2 VALUES (1,1,1,100),(2,1,2,200),(3,1,3,0.5),(4,1,4,70),(5,1,5,100)
    
    DECLARE @SQL NVARCHAR(MAX), @Cols NVARCHAR(MAX)
    
    SELECT @Cols = STUFF((SELECT ', ' + QUOTENAME([name]) FROM T1 ORDER BY [name] FOR XML PATH('')),1,2,'')
    
    SET @SQL = 'SELECT * FROM (SELECT T2.ClientID, T2.T2Value, T1.Name as AttrName 
    from T2 INNER JOIN T1 ON T2.AttrID = T1.AttrID) src
    PIVOT (max(T2Value) FOR AttrName IN (' + @Cols + ')) pvt'
    
    EXECUTE (@SQL)
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by pauldube Wednesday, March 07, 2012 2:59 PM
    Wednesday, March 07, 2012 2:23 PM
  • This is great but now I have to add a bit of complexity.

    In fact table T2 is like this:
    CREATE TABLE T2
    (T2ID INT,    -- identifier of the value
    ClientID INT,    -- Identifier of the client
    AttrID INT,    -- Value Attribute ID
    T2Value FLOAT NULL,     -- Value for given attribute
    T2Text VARCHAR(50) NULL    -- Text value for given attribute
    );

    So, for some attributes I have a T2Value (and T2Text IS NULL) and for others I have a T2Text (and T2Value IS NULL).

    So I'd need to display when not null T2Value else T2Text.

    Any way to do this?

    Wednesday, March 07, 2012 3:04 PM
  • Try in the @SQL definition:

    SET @SQL = 'SELECT * FROM (SELECT T2.ClientID,

    CASE WHEN T2.T2Value IS NULL then T2.T2Text ELSE CAST(T2Value as VARCHAR(50)) END as T2Value,

    T1.Name as AttrName from T2 INNER JOIN T1 ON T2.AttrID = T1.AttrID) src PIVOT (max(T2Value) FOR AttrName IN (' + @Cols + ')) pvt'



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi NModerator Wednesday, March 07, 2012 3:07 PM
    • Marked as answer by pauldube Wednesday, March 07, 2012 3:11 PM
    Wednesday, March 07, 2012 3:07 PM
  • Great! Thanks a million!
    Wednesday, March 07, 2012 3:11 PM