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.
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.
Thanks
Amit Shah
Loop1 Systems