Is a Sql Dynamic Pivot My Best Option?

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.

Thanks!

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(
                 (
                     SELECT DISTINCT
                            ','+QUOTENAME(a.attributeName)
                     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 = 

'SELECT *   
INTO ' + @TableName + '
FROM
(SELECT [cliID], '+@cols+ 'from (SELECT 
           [cliID],
           [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 (@query);

EXECUTE ( 'SELECT * from ' + @TableName )  

Open in new window

ppostonPresident/OwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
No, you don't need dynamic SQL.
Did you try to write the query without using dynamic SQL? It should work.
0
Mark WillsTopic AdvisorCommented:
Hmmmm... Interesting problem.

Certainly turning  customAttributesForClients rows into columns is a challenge. Been a while since using the pre-2005 (ie pre-Pivot) cross-tab type SQL. Always a challenge.  

Is there a maximum # custom attributes ?
Is there any constraint on how or what  users can track their own customers ?
Is there any consolidation - e.g. isnt a 'isMarried' the same question as as 'isSingle'  (and realise it was just an example) ?
Are the Attribute answers binary - like yes/no - or descriptive ?
Is there any need for column alignment - as in if User1 tracks IsMarried, User2 tracks IsSingle, User3 tracks IsRallyDriver - could they collectively be known as Attribute 1 (as a column) with the IsMarried (binary) as data in that column ?

Truth be known, you probably need to do your joins inside the stored proc and include those extra tables + columns...

Which means not doing select *

And dont think you need to select into @TableName - but you might have other uses.... I see your routine is per @cliID - so must be part of a bigger picture.

If you want to dummy up some data, would enjoy the challenge :)
0
ppostonPresident/OwnerAuthor Commented:
A little background.  I am self taught in development so some I my responses may seem elementary.  I supply a site for non profits helping them manage client information.  Up until now we have not allowed the user agencies to track custom data, but now we are needing to do that.  In the sample database attached, the client table is where we have stored all of the static data that we collect (ie. name, address, birthday, etc).  Now we are adding the ability for agencies to track data that they need which is not tracked in our current model.  Since every agency has different needs we have developed a way for them to enter their own custom fields whether it be numeric, currency, text, drop down list, etc.  We followed the model by Scott Mitchell in this article (http://www.4guysfromrolla.com/articles/082008-1.aspx) as it seemed best to meet our needs in our asp.net site.  We like the way it is working as far as capturing custom data, but now we need to be able utilize that data for reporting purposes.  That's where I have reached a road block in how to best accomplish that.  For instance I would like to be able pull a client contact list which could be filtered by any of the fields whether it's the city from the client table or a custom field such as nationality.  To do this I would assume I need to return client data (along with custom data) on a single row.

The sample database below contains the following tables:

client - contains the original data pertaining to each client
customAttributeDataTypes - what type of data the custom field begin created by the client is
customPickListOptions - contains the values for drop down lists that are created
customAttributesForAgencies - contains the custom fields that each individual agency creates for capturing specific data
customValuesForClients - contains the repsonses for each custom field for each client

When you see "cliID" that is the relationship to the client table id (client.id).  When you see "agnID" that is the relationship to the agencies table (not included in sample).

I hope this provides the necessary information that you may need to help.

Thanks so much!

Phil
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark WillsTopic AdvisorCommented:
Hi Phil,

Great background information. Helps a lot, thank you very much.

One small issue... nothing attached :)

Cheers,
Mark
0
ppostonPresident/OwnerAuthor Commented:
Forgot to choose upload...sorry about that.
testDB.mdf
0
Mark WillsTopic AdvisorCommented:
OK Got it... So you would like to see all client data as a row.

I can do the SQL part, but reluctant to get engaged with the programming asp.net side of things - is that OK ?

Can always add a few more TA's for you to attract more attention....

In the meantime, i will have a look at your testDB.

Cheers,
Mark Wills
0
ppostonPresident/OwnerAuthor Commented:
I can handle the asp side it's just getting that data into a single row for each client that I couldn't get my head around.  Once I create the Stored Procedure it's no problem to get wired up to the code.

Thanks so much!

Phil
0
Mark WillsTopic AdvisorCommented:
Well, I think the easiest way to achieve would be adding in the join to client as part of your @query

Something like
SET @query = 

'SELECT *      
INTO ' + @TableName + '
FROM
client c inner join
(SELECT [cliID], '+@cols+ '
 from (SELECT [cliID],[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
on c.id = dt.cliid'  ;

Open in new window

So very dangerous to select * into a table. Should check for naming conflicts first

Also, when testing, had to do
exec ('if object_id(''tempdb..'+@tablename+''') is not null drop table '+ @tablename)

Open in new window

so the select into would work.

Would also suggest your final select is more like
EXECUTE ( 'SELECT * from ' + @TableName +' where id = '+@cliid)  

Open in new window


There are a couple of things we could tidy up, but let's get it working for you first....
0
ppostonPresident/OwnerAuthor Commented:
That looks really, really good.  Just a couple of more things and I'll leave you alone.  First, I probably need to be able to filter this by the agnID that's in the customValuesForClients table.  What I'll be doing is allowing each individual agency to pull a client list of their clients.  I tried a couple of things but I couldn't get the "agnID" into the rows.  I've never used a query like this before so I'm still trying to wrap my head around it and figure out each part.

Secondly, I'm using the @@spid with the @tableName in case 2 or more agencies happening to be using this procedure at the same time since it's a global table so there would be no conflict (I assume that's right).  So, should I manually drop that table at the end?

And lastly, it's been a while since I've used Experts Exchange and I want to make sure I credit you as I should because this is life saver!!  Do I just mark your response as the Best Answer when I'm finished or is there something else I need to do?

Thanks!
0
Mark WillsTopic AdvisorCommented:
Yep, using @@spid is a good move. Not sure if you need a global temp table (ie the @@ instead of @) - unless you need it for other processes - but if you plan to remove at the end, then dont think global temp is needed. Regardless, always best to remove when no longer needed - in which case, also doesnt matter if it is a global temp table. Bit of a catch-22.

Use SPID and cleanup when you can.

For agnID, is that being passed to the stored proc ? Let us assume it is a) available and b) being passed as parameter @agnID

In which case, the only columns they should be seeing are theirs - right ? So, we should be filtering on customAttributesForAgencies.

So, when building columns, filter for agency in that part. I would be doing :
FROM customAttributesForAgencies a JOIN customValuesForClients v ON v.attributeID = a.customAttributeID  WHERE a.agnID = @agnid and cliID = @cliID FOR XML PATH(''), TYPE

Open in new window


We could go a step further and include v.agnid = @agnid but really is more overkill....

But if we want to open up the query for multiple clients within an agency, then you would replace the cliID = @cliID with v.agnID = @agnID and that would give all the agency custom columns, regardless of client.

Does that make sense ?

As for closing, you pick 'best' and any assisted. Then you can also adjust point splits. It is easier now days than the old grading style of closure.

Cheers,
Mark Wills
0
ppostonPresident/OwnerAuthor Commented:
Getting late here so I'm not concentrating too good...have to edit this comment.  I actually had tried what you suggested and it filters out all custom data fields except those of the current agency which is correct.  It doesn't filter out the row from the final table.  For instance with the agnID of 791 passed in, I should retreive only John Doe and not Sara Poston because her custom data was only created by agnID 857.  So basically I am only returning clients where the agnID parameter matches the agnID of the customValuesForClients.

Phil
0
Mark WillsTopic AdvisorCommented:
Will have a look... Get some rest :)
0
Mark WillsTopic AdvisorCommented:
OK, three things to tidy up...

1) The @cols build (which you know about)
SET @cols = STUFF(
                 (
                     SELECT DISTINCT 
                            ','+QUOTENAME(a.attributeName)
                     FROM customAttributesForAgencies a JOIN customValuesForClients v ON v.attributeID = a.customAttributeID  WHERE a.agnID = @agnid and cliID = @cliID FOR XML PATH(''), TYPE
                 ).value('.', 'nvarchar(max)'), 1, 1, '');

Open in new window

2) is where it gets re-introduced on the Agency join to Customer, much the same as above, but in the @query in the innermost subquery 'x' join
SET @query = 

'SELECT *   
INTO ' + @TableName + '
FROM
client c inner join
(SELECT [cliID], '+@cols+ '
 from (SELECT [cliID],[customValue] AS [cValue], [attributeName] AS [aName]
       FROM customAttributesForAgencies a join customValuesForClients c on c.attributeID = a.customAttributeID and a.agnID = '+ cast(@agnID as varchar(20)) + ' )x
	   pivot (max(cValue) for aName in ('+@cols+')) p) dt
on c.id = dt.cliid'  ;

Open in new window

3) from a previous suggestion, you can make sure you retrieve the correct customer in the final exec(select ... )
EXECUTE ( 'SELECT * from ' + @TableName ) --+' where id = '+@cliid)              -- to include final check remove ')  --' after @Tablename 

Open in new window

OR

If you wanted to, we also have agnID as part of client, so we can not only use the agnID from the 'x' inner join, but we can also add in extra checks in the @query  outer query part where we join client
on c.id = dt.cliid and c.agnID = ' + cast(@agnID as varchar(20))  ;

Open in new window

0
ppostonPresident/OwnerAuthor Commented:
Thanks for all of your help.  This worked great!!  Also, I've learned so much about this type of query, I appreciate all of the explanations. This will definitely help the non profits we work with as their funding is dependent on data that supports what they are accomplishing.  With continued funding they can continue to help those in poverty.

Perhaps one last question if you don't mind...to help me learn a little more.  From what I've read, with this query the table and data are not available outside of the query itself, correct?  If I wanted to use the data from @TableName to join with another #temp table that would not work if I understand my research correctly.

Thanks again for all of the help!!

Phil
0
Mark WillsTopic AdvisorCommented:
Well, what you are doing is using dynamic SQL to perform a pivot query.

If there is one thing that SQL Server is not so good at is Pivot or Crosstab. Reason being that SQL Server insists on having column names explicitly listed. Thats where the dynamic SQL comes into play - to help build a list of column names.

Because we use Dynamic SQL you are basically backed into using a Stored Procedure.

I have written about it in an Article about it that should explain more : http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html.

now, you can nest stored procedure such that, data from one procedure becomes available to the other. Use of Temp tables does have its challenges because they are not persistent - thats when we start using the global temp table - to make data available outside of the stored procedure.

Back on Pivot, it is normally constructed for reporting purposes, or limited to where resulting column names can be predicted and exposed. Because of this, it is normally the last step in presenting data.

Which means, structure your data collection with joins and collections and then do the pivot as a last step - even if that means calling a (sub) procedure to supply just the Pivot element.

Anyway, have a read of that Article, it will shed a bit more light on the subject.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ppostonPresident/OwnerAuthor Commented:
Thanks again for all of the help.  I will look at that article.  Maybe I'll call on you again down the road...but hopefully not too often (ha).

Phil
0
Mark WillsTopic AdvisorCommented:
A pleasure to be able to help. Enjoyed the challenge.

And thank you very much for your preparedness to share your issues. I will now delete the testDB. Which was in fact a huge help....

Looking forward to more posts from you :)

Cheers,
Mark Wills
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.