Formating a Select Statement
-
I've got a table that uses unique identifiers for each of the QuestionResponses (thanks DotNetNuke!). Currently I am pulling the data using the following code:
SELECT top 15 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') ORDER BY ResponseDateTime DESCEach Response entry has a DynamicQuestionID associated with it. Running the query above gives me the following results:
Response is obviously the field name and each result is based on the DynamicQuestionID (in order in the code above).
Now for my question:
How can I take my Select statement and put each of the DynamicQuestionID results in their own columns?
-
not sure what happened but your results section does not display
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
-
Dan.Humphries (5/13/2010)
not sure what happened but your results section does not display
-
If you can give us an example of your table, data, and an example result set, this should not be to difficult to help you with. Please read the article in my signature line and provide us the table def and sample data in a readily consumable form (inserts).
-
John Rowan (5/13/2010)
If you can give us an example of your table, data, and an example result set, this should not be to difficult to help you with. Please read the article in my signature line and provide us the table def and sample data in a readily consumable form (inserts).edit: hold on...now I understand what you want. Just gotta grab the crap outta SQL 2k5. 😮
I'll attempt make it cleaner:
SELECT top 15 Response, DynamicQuestionIDFROM 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'
)
ORDER BY ResponseDateTime DESC
There are only four records associated with each of the DynamicQuestionID which outputs to:
Response
Team-Max Gear
John Dobbs
5/90
9:1010
I want to take each row, after Response (which is the header pulled from the Select Top 15 RESPONSE) and add a column with each of these names:
Presentee, Factory, Date, Time
While typing this out I realized that the first two DyanmicQuestionID's should be swapped in order to correctly line up with the order I want displayed. heh.
-
Your formatting is not the problem. We need to know the table structure and see some sample data to help. Can you post your DDL for DynamicForms_QuestionResponse? Also, include some sample data. Again, read the article in my signature line.
-
John Rowan (5/13/2010)
Your formatting is not the problem. We need to know the table structure and see some sample data to help. Can you post your DDL for DynamicForms_QuestionResponse? Also, include some sample data. Again, read the article in my signature line.Yea, my apologizes. I edited my response while you were responding to indicate I now understand.
USE [DOTNN]
GO
/****** Object: Table [dbo].[DynamicForms_QuestionResponse] Script Date: 05/13/2010 13:08:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DynamicForms_QuestionResponse](
[DynamicQuestionResponseID] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_DynamicForms_QuestionResponse_QuestionResponseID] DEFAULT (newid()),
[DynamicQuestionID] [uniqueidentifier] NULL,
[Response] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserID] [int] NULL,
[SortOrder] [int] NULL CONSTRAINT [DF_DynamicForms_QuestionResponse_SortOrder] DEFAULT ((0)),
[InActive] [int] NULL CONSTRAINT [DF_DynamicForms_QuestionResponse_InActive] DEFAULT ((0)),
[ResponseDateTime] [smalldatetime] NULL CONSTRAINT [DF_DynamicForms_QuestionResponse_ResponseDateTime] DEFAULT (getdate()),
[UniqueResponseID] [uniqueidentifier] NULL,
CONSTRAINT [PK_DynamicForms_QuestionResponse] PRIMARY KEY CLUSTERED
(
[DynamicQuestionResponseID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
The other fields in this table are not needed but are created as part of module I am using in DotNetNuke.
Where do I grab the test data for you? I'm using SQL SMSE 2005.
-
acogswell (5/13/2010)
I want to take each row, after Response (which is the header pulled from the Select Top 15 RESPONSE) and add a column with each of these names:
Presentee, Factory, Date, Time
While typing this out I realized that the first two DyanmicQuestionID's should be swapped in order to correctly line up with the order I want displayed. heh.
OK, we're 1/2 way there. Now, you say you want to add those 4 columns. Where does that data come from?
Also, can you give me a few sample rows from your DynamicForms_Questionresponse table?
-
John Rowan (5/13/2010)
acogswell (5/13/2010)
I want to take each row, after Response (which is the header pulled from the Select Top 15 RESPONSE) and add a column with each of these names:
Presentee, Factory, Date, Time
While typing this out I realized that the first two DyanmicQuestionID's should be swapped in order to correctly line up with the order I want displayed. heh.
OK, we're 1/2 way there. Now, you say you want to add those 4 columns. Where does that data come from?
Also, can you give me a few sample rows from your DynamicForms_Questionresponse table?
The data comes from an input on dynamic form creator.
How do I properly grab the sample rows and display them here? Your guide doesn't have that step for 2k5 🙂
-
It should not matter where the data comes from. Grab (SELECT) a few rows from you table and post them in the forum.
-
This isn't going to look pretty...:hehe:
d057c210-1f77-4c88-9807-23d7ff0d94a052e94064-b993-46ab-aa3f-3a9f42f8f4bd5/311005/13/2010 1:46:00 PM8624335f-ca90-4cc2-9ec3-388b7b2857e0
10405e79-a94a-4b31-ada3-594e3d9b7c77a23b7c9f-9721-48b6-8440-6d683ec41923Adam11005/13/2010 1:46:00 PM8624335f-ca90-4cc2-9ec3-388b7b2857e0
3849b2a2-37fd-4886-95f0-7fd61487e6222e9fe800-fa58-4b1f-b55e-52ea7e7705239:10 AM11005/13/2010 1:46:00 PM8624335f-ca90-4cc2-9ec3-388b7b2857e0
589fae6d-f5c2-4858-99d2-f87c3fc14b75c31a5c91-7429-4933-b2da-f73a036505b1Factory 211005/13/2010 1:46:00 PM8624335f-ca90-4cc2-9ec3-388b7b2857e0
c1b47cc6-3aff-4cd9-8996-838326e9070da23b7c9f-9721-48b6-8440-6d683ec41923John Dobbs11005/13/2010 10:41:00 AMed80718e-157a-4970-8a79-c7df29083b1f
be368f4b-0481-4b85-a71f-ce3621379d59c31a5c91-7429-4933-b2da-f73a036505b1Team-Max Gear11005/13/2010 10:41:00 AMed80718e-157a-4970-8a79-c7df29083b1f
45a341d3-6e7d-496c-9487-7a65fffb8c4a52e94064-b993-46ab-aa3f-3a9f42f8f4bd5/9011005/13/2010 10:41:00 AMed80718e-157a-4970-8a79-c7df29083b1f
d589faca-f98f-4d03-991f-3bfe48917e752e9fe800-fa58-4b1f-b55e-52ea7e7705239:101011005/13/2010 10:41:00 AMed80718e-157a-4970-8a79-c7df29083b1f
-
OK. Now we have sample data to work with...Here it is in a usable format:
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 * FROM @DynamicForms_QuestionResponse
Now, you want to add on columns to the result set? Where does Presentee, Factory, Date, Time come from?
-
Now, you want to add on columns to the result set? Where does Presentee, Factory, Date, Time come from?
The four categories are what each of the responses represent.
I would show you the form, but it is all internal. I have a user fill out four fields (the ones above), the Dynamic Generator sends an email to all staff formatted all nice and snazzy, and then I populate an output based on what they entered.
-
acogswell (5/13/2010)
This isn't going to look pretty...:hehe:d057c210-1f77-4c88-9807-23d7ff0d94a052e94064-b993-46ab-aa3f-3a9f42f8f4bd5/311005/13/2010 1:46:00 PM8624335f-ca90-4cc2-9ec3-388b7b2857e0
10405e79-a94a-4b31-ada3-594e3d9b7c77a23b7c9f-9721-48b6-8440-6d683ec41923Adam11005/13/2010 1:46:00 PM8624335f-ca90-4cc2-9ec3-388b7b2857e0
3849b2a2-37fd-4886-95f0-7fd61487e6222e9fe800-fa58-4b1f-b55e-52ea7e7705239:10 AM11005/13/2010 1:46:00 PM8624335f-ca90-4cc2-9ec3-388b7b2857e0
589fae6d-f5c2-4858-99d2-f87c3fc14b75c31a5c91-7429-4933-b2da-f73a036505b1Factory 211005/13/2010 1:46:00 PM8624335f-ca90-4cc2-9ec3-388b7b2857e0
c1b47cc6-3aff-4cd9-8996-838326e9070da23b7c9f-9721-48b6-8440-6d683ec41923John Dobbs11005/13/2010 10:41:00 AMed80718e-157a-4970-8a79-c7df29083b1f
be368f4b-0481-4b85-a71f-ce3621379d59c31a5c91-7429-4933-b2da-f73a036505b1Team-Max Gear11005/13/2010 10:41:00 AMed80718e-157a-4970-8a79-c7df29083b1f
45a341d3-6e7d-496c-9487-7a65fffb8c4a52e94064-b993-46ab-aa3f-3a9f42f8f4bd5/9011005/13/2010 10:41:00 AMed80718e-157a-4970-8a79-c7df29083b1f
d589faca-f98f-4d03-991f-3bfe48917e752e9fe800-fa58-4b1f-b55e-52ea7e7705239:101011005/13/2010 10:41:00 AMed80718e-157a-4970-8a79-c7df29083b1f
acogswell,
you can download a free tool called SSMS Tools pack (www.ssmstoolspack.com) that is a plug-in to SSMS. Then you can right click on a table and select SSMS from the menu and generate insert statements. The tool has other benefits as well that you could explore. Plus the price is right.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
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?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply