SQL Unpivot data out based on a join, is this possible?

user3494110 0
Viewed 38

I'm trying to eliminate having a long running list of insert queries in a stored procedure. This is for updating a smartsheet from SQL server (below is all SQL/SQL server).

I have a bunch of metrics on a pretty standard table. Small example below:

TABLE A

Site_Name    Revenue   Expenses  MiscOverhead
site 1        80000     10000       1000
site 2        90000     9000         500
site 3        50000     2000         200

Now, in order to update smartsheet, you need to have a row Id and a column Id. The row Id is not an issue however I need to bump Table A up against a mapping table that has the name of the column, and then the corresponding column Id. So for instance the mapping table would look similar to this:

TABLE B
ColumnName    ColumnId
Revenue        xyz11
Expenses       zzz33
MiscOverhead   jjj34

In order to get what I am looking for, the only way I know how to do this, is to write a bunch of union statements and multiple inserts into a temp table to get my output. Does anyone know of way of how to do this in a single query in sql? I wasn't sure if there was a way to do some type of fancy pivot join between the column name on Table A and B. Below is how the output data needs to look:

RESULT NEEDED
Site_Name    ColumnName         ColumnId     ColumnValue
site 1        Revenue             xyz11          80000
site 1        Expenses            zzz33          10000
site 1        MiscOverhead        jjj34          1000
site 2        Revenue             xyz11          90000
site 2        Expenses            jjj34          9000

etc.etc. for all sites

Any advice is appreciated thank you very much.

sql-server-2012 unpivot tsql sql-server sql
2 Answers

Assuming numerous or variable columns... This will dynamically UNPIVOT your data without having to use dynamic SQL.

Select A.Site_Name
      ,ColumnName = C.Item
      ,ColumnID   = D.ColumnId
      ,ColumnValue= C.Value
 From  TableA A
 Cross Apply ( values ((Select A.* for XML RAW,Type))) B(XData)
 Cross Apply (
               Select Item  = replace(xAttr.value('local-name(.)', 'varchar(100)'),'_x0020_',' ')
                     ,Value = xAttr.value('.','varchar(150)')   -- <<Use the appropriate datatype
                From  B.XData.nodes('//@*') xNode(xAttr)
            ) C
 Join  TableB D on C.Item=D.ColumnName  
 Where Item not in ('Site_Name','OtherCols','ToExclude')

Results

enter image description here

John Cappelletti0

You can just unpivot using CROSS APPLY (VALUES

SELECT
  a.Site_Name,
  u.ColumnName,
  b.ColumnId,
  u.ColumnValue
FROM Table_A a
CROSS APPLY (VALUES
    ('Site_Name',    CAST(a.Site_Name AS nvarchar(max))),
    ('Revenue',      a.Revenue),
    ('Expenses',     a.Expenses),
    ('MiscOverhead', a.MiscOverhead)
) u(ColumnName, ColumnValue)
JOIN Table_B b ON b.ColumnName = u.ColumnName
Charlieface0
Related