May 22, 2009 at 4:39 am
Hi all,
I have a table containing information about a person, lets call it dbo.contactinfo.
this table is in the format as below
person_id, question, answer (this is simplified but it communicates my point)
As you can see all info is currently stored as name and value pairs with multiple records for a person(id).
Now i know i can use the pivot function to turn this on its side but that requires you to know what the values of question field will be. I also know I can write some code to dynamically build sql to combat this but this needs to be run each time you need the data and I really just need a view that I can then join into other queries that I dont have to keep updating each time a new "question" comes into the table.
Any ideas?
Thanks
May 22, 2009 at 4:46 am
This can be done dynamically without having to change the code.
Could you supply some sample data and what the output should be please 🙂
thanks
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2009 at 4:59 am
Hi thanks for the quick response.
Basically the data looks like below:
PersonID Question Answer
123 Favourite Colour Blue
123 Age 21
786 Car Ford
900 Car Lexus
etc
I then want a view to pivot the question column, which is variable and could be anything and any number of unique values as below.
PersonID Favourite_Colour Age Car ......
123 Blue 21 NULL
786 NULL NULL Ford
900 NULL NULL Lexus
As I said in my initial post I know I can do this using pivot but as the values in question is not a set list I can't see how I can get this into a view
Thanks again
May 22, 2009 at 5:05 am
Ok I hope I'm understanding what you saying but sorry if I've misunderstood:-)
This code you will see that if you add extra rows with different questions to the table before the pivot you will not need to change the actually pivot part of the query to get your results:
CREATE TABLE contactinfo
(
person_id INT,
question VARCHAR(1000),
answer VARCHAR(2000)
)
INSERT INTO contactinfo
SELECT 123 ,'Favourite Colour', 'Blue' UNION ALL
SELECT 123 ,'Age','21' UNION ALL
SELECT 786 ,'Car','Ford' UNION ALL
SELECT 900 ,'Car','Lexus' UNION ALL
SELECT * FROM contactinfo
--PIVOT
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT question
FROM contactinfo
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
EXEC ('SELECT *
FROM
(
SELECT *
FROM contactinfo
) t
PIVOT (MAX(answer) FOR question IN (' + @ColsList + ')) PVT')
DROP TABLE contactinfo
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2009 at 5:21 am
Thank you for the response again.
Your response is part way towards what I need.
the next stage, and this is where I am stuggling, is that I need basicsally the result of the dynamically executed sql available in a view so that I can then join that view to other tables in a query so that I can, in my example, display all the questions and answers against the person along with all the persons details that are stored in another table.
Thanks
May 22, 2009 at 5:38 am
mmm that is going to be tricky ...
Could you not just join your other tables to the dynamic query:
--TEST DATA
--**************************************
CREATE TABLE contactinfo
(
person_id INT,
question VARCHAR(1000),
answer VARCHAR(2000)
)
CREATE TABLE personinfo
(
person_id INT,
Name VARCHAR(1000),
Surname VARCHAR(2000)
)
INSERT INTO contactinfo
SELECT 123 ,'Favourite Colour','Blue' UNION ALL
SELECT 123 ,'Age','21' UNION ALL
SELECT 786 ,'Car','Ford' UNION ALL
SELECT 900 ,'Car','Lexus'
INSERT INTO personinfo
SELECT 123 ,'Chris','Stobbs' UNION ALL
SELECT 786 ,'Joe','Blogs' UNION ALL
SELECT 900 ,'David','Morrison'
SELECT * FROM contactinfo
--**************************************
CREATE PROCEDURE PersonData
AS
--PIVOT
DECLARE @Colslist VARCHAR(MAX)
DECLARE @Cols TABLE (Head VARCHAR(MAX))
INSERT @Cols (Head)
SELECT DISTINCT question
FROM contactinfo
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'
FROM @Cols t
EXEC ('SELECT pvt.*,p.Name,p.Surname
FROM
(
SELECT *
FROM contactinfo
) t
PIVOT (MAX(answer) FOR question IN (' + @ColsList + ')) PVT
LEFT JOIN personinfo p ON p.person_id = PVT.person_id')
GO
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2009 at 6:24 am
Yes I potentially could but the reason for the query is to populate an SSRS report and I'm not sure that SSRS allows dynamic sql?
May 22, 2009 at 6:32 am
Can't you use a stored procedure as the source for the report?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2009 at 6:40 am
I think I tried that and it doesnt like it?
just did the basic exec
is there another way to do this?
Sorry I've been banging my head against the wall a bit with this for a while, I normally like to figure these things out myself but we all need help sometimes 😀
Thanks for continuing to help out 🙂
May 22, 2009 at 6:46 am
no problem.
The only other thing I think of but I have feeling it will perform badly, is to use a CLR and call that.
But I'd be interested to know what error you got using a stored procedure SSRS, I'm sure I used to use them all the time.
It might be that the permissions are not correct and perhaps thats why the dynamic is not working correctly in SSRS
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
May 22, 2009 at 6:51 am
I'll give the stored procedure thing a go in a test report and let you know how i get on
🙂
Thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply