Formating a Select Statement

Question

Formating a Select Statement

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

acogswell SSC Veteran Points: 268 More actions · Answer 1

John Rowan (5/13/2010)
So based on the 8 rows of sample data that you've shown us. What do you want the result set to look like? Can we figure out what to put into the other 3 columns based on the data?

Yes, hopefully this crude image will do. Each black box after the initial data will have the next set of info that is populated into the table.

For example, 'c31a5c91-7429-4933-b2da-f73a036505b1' = Presentee, which should be in the first column.

John Rowan SSC Guru Points: 56440 More actions · Answer 2

Well, this gives you the results that you are looking for (I think) based on your original query's DynamicQuestionIDs. But my question is, how do you know from the DynamicQuestionID if the data represents a Presentee, Factory, Date, or Time? It seems to me like we're missing some sort of identifier. My code works, but only for this set of responses. As soon as you want to look at a different set of values, the query would need to change.

DECLARE @DynamicForms_QuestionResponse TABLE(

DynamicQuestionResponseID uniqueidentifier ,

DynamicQuestionID uniqueidentifier NULL,

Response nvarchar(4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

UserID int NULL,

SortOrder int NULL,

InActive int NULL,

ResponseDateTime smalldatetime NULL,

UniqueResponseID uniqueidentifier NULL

)

INSERT INTO @DynamicForms_QuestionResponse

SELECT 'd057c210-1f77-4c88-9807-23d7ff0d94a0', '52e94064-b993-46ab-aa3f-3a9f42f8f4bd', '5/3', 11, 0, 0,'5/13/2010 1:46:00 PM', '8624335f-ca90-4cc2-9ec3-388b7b2857e0' UNION ALL

SELECT '10405e79-a94a-4b31-ada3-594e3d9b7c77', 'a23b7c9f-9721-48b6-8440-6d683ec41923', 'Adam', 11, 0, 0, '5/13/2010 1:46:00 PM', '8624335f-ca90-4cc2-9ec3-388b7b2857e0' UNION ALL

SELECT '3849b2a2-37fd-4886-95f0-7fd61487e622', '2e9fe800-fa58-4b1f-b55e-52ea7e770523', '9:10 AM', 11, 0, 0, '5/13/2010 1:46:00 PM', '8624335f-ca90-4cc2-9ec3-388b7b2857e0' UNION ALL

SELECT '589fae6d-f5c2-4858-99d2-f87c3fc14b75', 'c31a5c91-7429-4933-b2da-f73a036505b1', 'Factory 2', 11, 0, 0, '5/13/2010 1:46:00 PM', '8624335f-ca90-4cc2-9ec3-388b7b2857e0' UNION ALL

SELECT 'c1b47cc6-3aff-4cd9-8996-838326e9070d', 'a23b7c9f-9721-48b6-8440-6d683ec41923', 'John Dobbs', 11, 0, 0, '5/13/2010 10:41:00 AM', 'ed80718e-157a-4970-8a79-c7df29083b1f' UNION ALL

SELECT 'be368f4b-0481-4b85-a71f-ce3621379d59', 'c31a5c91-7429-4933-b2da-f73a036505b1', 'Team-Max Gear', 11, 0, 0, '5/13/2010 10:41:00 AM', 'ed80718e-157a-4970-8a79-c7df29083b1f' UNION ALL

SELECT '45a341d3-6e7d-496c-9487-7a65fffb8c4a', '52e94064-b993-46ab-aa3f-3a9f42f8f4bd', '5/90', 11, 0, 0, '5/13/2010 10:41:00 AM', 'ed80718e-157a-4970-8a79-c7df29083b1f' UNION ALL

SELECT 'd589faca-f98f-4d03-991f-3bfe48917e75', '2e9fe800-fa58-4b1f-b55e-52ea7e770523', '9:1010', 11, 0, 0, '5/13/2010 10:41:00 AM', 'ed80718e-157a-4970-8a79-c7df29083b1f'

SELECT [a23b7c9f-9721-48b6-8440-6d683ec41923] as Presentee,

[c31a5c91-7429-4933-b2da-f73a036505b1] as Factory,

[52e94064-b993-46ab-aa3f-3a9f42f8f4bd] as Date,

[2e9fe800-fa58-4b1f-b55e-52ea7e770523] as Time

FROM (

SELECT Response,

DynamicQuestionID

FROM@DynamicForms_QuestionResponse

WHERE DynamicQuestionID in ('a23b7c9f-9721-48b6-8440-6d683ec41923','c31a5c91-7429-4933-b2da-f73a036505b1','52e94064-b993-46ab-aa3f-3a9f42f8f4bd','2e9fe800-fa58-4b1f-b55e-52ea7e770523')

) as t

PIVOT (MIN(Response) FOR DynamicQuestionID IN

([a23b7c9f-9721-48b6-8440-6d683ec41923], [c31a5c91-7429-4933-b2da-f73a036505b1], [52e94064-b993-46ab-aa3f-3a9f42f8f4bd], [2e9fe800-fa58-4b1f-b55e-52ea7e770523])

) as PivotTable

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

acogswell SSC Veteran Points: 268 More actions · Answer 3

John Rowan (5/13/2010)
Well, this gives you the results that you are looking for (I think) based on your original query's DynamicQuestionIDs. But my question is, how do you know from the DynamicQuestionID if the data represents a Presentee, Factory, Date, or Time? It seems to me like we're missing some sort of identifier. My code works, but only for this set of responses. As soon as you want to look at a different set of values, the query would need to change.

That is opening a whole 'nother can of worms ;).

Each Question on the Form is stored in a table called "Question". The DyanmicID is associated with that question and (sorry for the bad terminology) crosses over into the QuestionResponse table. To get the DQID I submitted the form, sorted by date submitted and grabbed the ID for the questions which I already knew which was what because of the answers and time that I submitted it. That even confused me when I read it....

Before I go on I need to say THANK YOU!! for all of your help. I've learned my lesson for future questions :).

Now..this could be the module I am using but I am receiving this error when inputting your code:

Error on Primary Select: Must declare the table variable "@DynamicForms_QuestionResponse".

Please to note: I am using SQLSelectedGridView for DotNetNuke, so I am very limited as to what I can do.

John Rowan SSC Guru Points: 56440 More actions · Answer 4

@DynamicForms_QuestionResponse is a table variable in my example. You'll want to remove the '@' symbol and replace it w/ dbo. if you want to run this same query against your real table. The example query runs against the table variable that I set up in the example.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

acogswell SSC Veteran Points: 268 More actions · Answer 5

EXCELLENT!!! You rock 🙂

Now how do I manipulate the code so it'll always display the Top 15 results in table for the DynamicQuestionID that I previously mentioned? I saw that you mentioned that I'd need to alter the code a bit in order to do that.