Vertical listing of reference table in nodes details page using unpivot and dynamic sql

Version 1

    A client asked for a way to use an reference table to pull values for a node.  This particular reference table has a list of all SiteCodes for the nodes.

    One way of doing it is to just dump the values.  But it’s very messy.  Plus want to see the values listed vertically, not horizontality.

    a1.png

    a2.png

    The easy way is to pull out the fields in a select. Which is good for a few values, but gets a bit tedious.   Using unpivot and dynamic sql, we can have sql generate the sql code for us. Dynamic SQL does not work at all using Custom table, however it does partially work in Report writer (you can also use SQL Management Studio)

     

    1) Put this in report writer:

    DECLARE

    @colsUnpivot AS NVARCHAR(MAX),

    @query AS NVARCHAR(MAX)

     

    select @colsUnpivot

      = stuff((select ','+quotename(C.column_name)

    from information_schema.columns as C

    where C.table_name = 'YourReferenceTableName'

    and C.column_name not like 'Status Date'    -- I took out this field because it was not needed

    for xml path('')), 1, 1, '')

    set @query

      = 'select Label, Value

    from ( select s.* from YourReferenceTableName s join nodes n on s.[site code] = n.sitecode where n.nodeid = ${nodeid}) x

    unpivot

    (

    Value

    for Label in ('+ @colsUnpivot +')

    ) u'

     

    select @query

    exec sp_executesql @query

     

    2) That will generate

    select Label, Value from ( select s.* from YourReferenceTableName s join nodes n on s.[site code] = n.sitecode where n.nodeid = 7477) x unpivot ( Value for Label in ([Site Code],[Office Status],……, Latitude],[Longitude]) ) u

     

    3) Replace the specific node with ${nodeid} and use as a datasource in a Custom Table

    select Label, Value from ( select s.* from YourReferenceTableName s join nodes n on s.[site code] = n.sitecode where n.nodeid = ${nodeid}) x unpivot ( Value for Label in ([Site Code],[Office Status],……, Latitude],[Longitude]) ) u

     

    4) That’s it.  You can use this in report writer or custom table to get a nice vertical listing.

    a3.png

    Thanks

    Amit Shah

    Loop1 Systems