CODEWITHSUNDEEP
sqlsql-server-2008
codingManiac
codingManiac

Reputation: 1700

Select name of column in pivot

I need to select the name of the column from which the pivot values comes from in the following pivot...

SELECT DISTINCT 
  filename,
  vals
FROM 
   (SELECT 
        filename,
        a1,
        a2,
        a3
   FROM 
      foo) p
UNPIVOT
   (vals FOR counts IN 
      (a1,a2,a3)
) AS bar

So if a value gets select into the vals column but is actually from a3 then I need a3 to be selected into another column within that record. So...

   Vals   |   AColName
  235.53      a3
  48.54       a3
  93.49       a1

Upvotes: 0

Views: 46

Answers (1)

donstack
donstack

Reputation: 2715

just use counts column in select as follows:

   SELECT DISTINCT 
  filename,
  vals,counts
FROM 
   (SELECT 
        filename,
        a1,
        a2,
        a3
   FROM 
      foo) p
UNPIVOT
   (vals FOR counts IN 
      (a1,a2,a3)
) AS bar

Upvotes: 1

Related Questions