Transposing rows from one table based on column values in another table

Question

I asked a similar question [here]: Transposing groups of rows with the same ID into another table but I don't want to have to write out each line for each attribute name. I want to be able to take rows in one column from TABLE1 (say there's 50 unique rows) and transpose them efficiently into TABLE2.

This is a small example of a table of ATTR_NMEs (attribute names) in one column:

  ID          ATTR_NME          ATTR_VAL
 1000           UPC            00015582981001
 1000          Price              15.99
 1000          Brand               Oreo
 1005           UPC            00038762291010
 1005          Price               12.50
 1005          Brand             Sargento
 1010           UPC            00198872499000
 1010          Price               4.99
 1010          Brand               Olay

So ID, UPC, Price, and Brand should be their own columns with the ATTR_VAL as the rows of data. HERE'S THE DIFFERENCE BETWEEN THIS QUESTION AND MY PREVIOUS ONE: say I have a table with 50 different ATTR_NMEs, I want to use that column of data with all those attribute names to create the new tranposed table without having to write the following 50 times for the different attributes:

MAX(CASE WHEN ATTR_NME = 'UPC' THEN ATTR_VAL END) AS UPC

I tried to declare a variable for the rows in the ATTR_NME column like this:

DECLARE @itm varchar(100);
SET @itm = (
      SELECT ATTR_NME
      FROM TABLE1
      );
SELECT ID,
       MAX(CASE WHEN ATTR_NME = @itm THEN ATTR_VAL END) AS [@itm]
FROM TABLE1
GROUP BY ID;

But that didn't work for multiple reasons; one being it wanted me to wrap an aggregate function around ATTR_NME where I SET @itm (but then I only got one attribute name). It also didn't label/name the column as I wanted it to. Is it even possible to do what I want to do? If so, how? TIA.

Dynamic SQL would be the only way this can be done that I know of and only if the columns are consistently named to the attr_name value — UnhandledExcepSean, Jan 25 '19 at 16:24

score 0 · Answer 1 · answered Jan 25 '19 at 16:19

Are you talking about Pivot?

If you need to pivot this table, you cannot avoid putting the ATTR_NME 50 times in the below query (see comment)

WITH A
AS
(
 SELECT '1000' as ID,          'UPC  ' as ATTR_NME,          '00015582981001' as ATTR_VAL
 UNION ALL SELECT '1000' as ID,          'Price' as ATTR_NME,          '15.99           ' as ATTR_VAL
 UNION ALL SELECT '1000' as ID,          'Brand' as ATTR_NME,          'Oreo            ' as ATTR_VAL
 UNION ALL SELECT '1005' as ID,          'UPC  ' as ATTR_NME,          '00038762291010' as ATTR_VAL
 UNION ALL SELECT '1005' as ID,          'Price' as ATTR_NME,          '12.50           ' as ATTR_VAL
 UNION ALL SELECT '1005' as ID,          'Brand' as ATTR_NME,          'Sargento        ' as ATTR_VAL
 UNION ALL SELECT '1010' as ID,          'UPC  ' as ATTR_NME,          '00198872499000' as ATTR_VAL
 UNION ALL SELECT '1010' as ID,          'Price' as ATTR_NME,          '4.99            ' as ATTR_VAL
 UNION ALL SELECT '1010' as ID,          'Brand' as ATTR_NME,          'Olay            ' as ATTR_VAL
)
SELECT *
FROM A
PIVOT
  (MAX(ATTR_VAL) FOR ATTR_NME in ([Price],[Brand],[UPC]) ) -- here you need to put all the 50 ATTR_NAME
  as PIVOTTABLE

Perhaps [use dynamic SQL](https://stackoverflow.com/questions/10404348), since if it's something like `Price1`, `Price2`, ... , `Price50` you can easily build the column list as a CSV string. — Elaskanator, Jan 25 '19 at 16:29

Transposing rows from one table based on column values in another table

1 Answers1