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.

Answer 1

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

Answer 2

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

Answer 3

I am lost with those examples. May be it's too early in the morning.

Can you provide code with my example?

Answer 4

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

Answer 5

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?

Answer 6

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

Answer 7

Great! Thanks a million!