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

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.

CodePudding user response：

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 (convert(xml,(Select A.* for XML RAW)))) B(XData)
 Cross Apply (
               Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
                     ,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

CodePudding user response：

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