Basically I have a client table and an activity table that holds each client's data in a single row and when they receive a service a single row in the activity table captures that. Simple enough. Now we are adding the ability for users to track their own custom data on each client. So I need to be able to return their client data (name, address, etc) PLUS the custom data created by the user. This is stored in a customAttributesForClients
table (isMarried, isSingle, etc). Each custom data created is contained in it's own row (ie. ROW1 - clientID, creatorID, isMarried. ROW2 - clientID, creatorID, isSingle).
I need to be able to return the data as such: clientID, creatorID, name, address, isMarried, isSingle. So each row would contain data from the tables and each row would be a distinct client. This way I can use the data in a gridview and easily filter that data.
I've looked at a Dynamic pivot with a global temp table and can retrieve the customAttributesForClients
okay on a single row, but can't figure out how to join that to my regular database tables for the client.
My question is then two fold. Am I using the right approach with a pivot table? If so how would I join that global temp table to my database table(s). I am pasting the current dynamic pivot below but please note the names in the example above differ from the query below. Also I'm using MSSQL 2012.
DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX), @cli NVARCHAR(MAX);
DECLARE @TableName varchar(100)
SET @TableName = '##MyTempTable' + cast( @@spid AS varchar(5))
DECLARE @TBL AS TABLE (ObjectName sysname NOT NULL, ColumnName sysname NULL);
SET @cli = (SELECT address from client WHERE id = @cliID)
SET @cols = STUFF(
FROM customAttributesForAgencies a JOIN customValuesForClients v ON v.attributeID = a.customAttributeID WHERE cliID = @cliID FOR XML PATH(''), TYPE
).value('.', 'nvarchar(max)'), 1, 1, '');
SET @query =
INTO ' + @TableName + '
(SELECT [cliID], '+@cols+ 'from (SELECT
[customValue] AS [cValue],
[attributeName] AS [aName]
FROM customAttributesForAgencies join customValuesForClients on customValuesForClients.attributeID = customAttributesForAgencies.customAttributeID
)x pivot (max(cValue) for aName in ('+@cols+')) p) dt' ;
EXECUTE ( 'SELECT * from ' + @TableName )