April 25, 2013 at 9:18 am
Hello, this is my first time posting here and I hope someone can help me. First, let me say that I am NO SQL Guru and this one has me stumped.
I have a table that stores the results of customer surveys and am tasked with displaying graphs of the results in an ASP.Net web page. I am trying to write a query for an sProc that will return a data table with the summaries data. The table looks something like this:
SurveyIDQ1Q2Q3Q4Q5HE1HE2HE3HE4HE5
22222222222
31313112345
40000000000
73000000000
84424233333
91223122222
102222211111
What I would like to return would be a data table that would summarize the counts of each value (0 to 4) in each column like:
Q1Q2Q3Q4 … etc
1222
2010
1341
1102
1101
Of course, the rows represent the values 0,1,2,3,4
Is this possible? I have experienced abject failure with everything I have tried! Thank you in advance for your help.
April 25, 2013 at 9:53 am
Hi and welcome to the forums!!!
I am 100% certain that what you are trying to do is possible. However, you need to post a lot more details before anybody can really offer much help. We need to see ddl and sample data. Please take a few minutes and read the first article in my signature, it will explain the best way to post this data and how to collect it. It does take some work on your part but you will rewarded with tested, fast and accurate code.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2013 at 10:29 am
I think something like this should at least be close:
SELECT
[values].value,
SUM(CASE WHEN Q1 = [values].value THEN 1 ELSE 0 END) AS Q1,
SUM(CASE WHEN Q2 = [values].value THEN 1 ELSE 0 END) AS Q2,
SUM(CASE WHEN Q3 = [values].value THEN 1 ELSE 0 END) AS Q3,
SUM(CASE WHEN Q4 = [values].value THEN 1 ELSE 0 END) AS Q4,
SUM(CASE WHEN Q5 = [values].value THEN 1 ELSE 0 END) AS Q5
FROM (
SELECT 0 AS value UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4
) AS [values]
LEFT OUTER JOIN dbo.tablename tn ON --or:: CROSS JOIN dbo.tablename tn
[values].value IN ( Q1, Q2, Q3, Q4, Q5 )
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 25, 2013 at 11:41 am
Sean,
Thank you for the welcome and for the link on how to create effective posts. Below you will find the script to recreate the table I am struggling with. For the purposes of this query I am only dealing with columns Q1 through HE7, but a solution that just dealt with Q1-Q3 would be enough help so that I could extrapolate how to do the rest.
--===== If the test table already exists, drop it
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblSurvey]') AND type in (N'U'))
DROP TABLE [dbo].[tblSurvey]
--===== Create the test table with
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[tblSurvey](
[SurveyID] [int] IDENTITY(1,1) NOT NULL,
[Q1] [int] NULL,
[Q2] [int] NULL,
[Q3] [int] NULL,
[Q4] [int] NULL,
[Q5] [int] NULL,
[HE1] [int] NULL,
[HE2] [int] NULL,
[HE3] [int] NULL,
[HE4] [int] NULL,
[HE5] [int] NULL,
[HE6] [int] NULL,
[HE7] [int] NULL,
[HEComments] [nvarchar](1500) NULL,
[HEDate] [datetime] NOT NULL,
[HE_testID] [int] NOT NULL,
CONSTRAINT [PK_dbo,tblSurvey] PRIMARY KEY CLUSTERED
(
[SurveyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--===== No special required conditions
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT [dbo].[tblSurvey] ON
--===== Insert the test data into the test table
INSERT INTO [dbo].[tblSurvey]
([SurveyID], [Q1], [Q2], [Q3], [Q4], [Q5], [HE1], [HE2], [HE3], [HE4], [HE5], [HE6], [HE7], [HEComments], [HEDate], [HE_testID])
SELECT 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'', 2011-04-07 15:36:02.870', 50 UNION ALL
SELECT 49, 2, 3, 2, 2, 1, 4, 4, 4, 4, 5, 3, 5, N'', '2012-04-09 15:15:00.957', 920 UNION ALL
SELECT 50, 1, 4, 2, 4, 1, 4, 4, 4, 4, 4, 3, 3, N'', '2012-04-22 14:38:48.863', 960 UNION ALL
SELECT 51, 1, 2, 2, 1, 1, 4, 5, 5, 5, 4, 3, 4, N'', '2012-04-26 12:10:12.967', 990 UNION ALL
SELECT 52, 1, 2, 2, 1, 1, 4, 5, 5, 5, 4, 3, 4, N'', '2012-04-26 12:11:55.837', 990 UNION ALL
SELECT 53, 1, 1, 2, 2, 1, 3, 3, 3, 3, 3, 3, 3, N'', '2012-05-04 09:29:10.473', 1030 UNION ALL
SELECT 54, 1, 2, 2, 1, 1, 5, 5, 5, 5, 3, 3, 4, N'', '2012-05-04 22:53:01.763', 1040 UNION ALL
SELECT 55, 1, 2, 2, 1, 2, 4, 3, 3, 4, 5, 3, 3, N'', '2012-05-06 13:14:43.873', 1050 UNION ALL
SELECT 56, 2, 3, 1, 2, 1, 5, 5, 5, 5, 5, 3, 5, N'', '2012-05-12 08:56:58.240', 1070 UNION ALL
SELECT 57, 1, 2, 2, 1, 1, 4, 4, 4, 4, 5, 4, 4, N'', '2012-05-13 03:40:50.630', 1080 UNION ALL
SELECT 58, 1, 2, 2, 1, 2, 4, 4, 4, 4, 4, 3, 4, N'', '2012-05-16 06:54:09.107', 1090 UNION ALL
SELECT 59, 4, 3, 1, 3, 1, 4, 5, 5, 5, 0, 4, 4, N'', '2012-05-18 17:22:12.220', 1120 UNION ALL
SELECT 60, 2, 2, 2, 2, 1, 4, 4, 4, 4, 4, 4, 4, N'', '2012-05-24 08:55:42.050', 1130 UNION ALL
SELECT 61, 1, 3, 1, 1, 2, 1, 1, 3, 1, 3, 1, 3, N'', '2012-06-01 10:14:27.920', 1140 UNION ALL
SELECT 62, 1, 3, 2, 1, 2, 4, 4, 5, 4, 4, 3, 5, N'', '2012-06-05 17:00:50.347', 1160 UNION ALL
SELECT 63, 2, 3, 1, 1, 1, 4, 4, 4, 4, 4, 3, 3, N'', '2012-06-05 17:18:25.070', 1180 UNION ALL
SELECT 64, 2, 3, 1, 1, 2, 5, 5, 5, 5, 5, 3, 3, N'', '2012-06-12 17:03:54.743', 1190 UNION ALL
SELECT 65, 1, 2, 2, 0, 1, 3, 3, 3, 3, 3, 3, 3, N'', '2012-06-20 15:45:41.720', 1200 UNION ALL
SELECT 66, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N'', '2012-06-21 19:47:08.570', 1220 UNION ALL
--===== Set the identity insert back to normal
SET IDENTITY_INSERT [dbo].[tblSurvey] OFF
April 25, 2013 at 11:46 am
Scott, thank you for your reply. When I run your solution like this:
SELECT
[values].value,
SUM(CASE WHEN Q1 = [values].value THEN 1 ELSE 0 END) AS Q1,
SUM(CASE WHEN Q2 = [values].value THEN 1 ELSE 0 END) AS Q2,
SUM(CASE WHEN Q3 = [values].value THEN 1 ELSE 0 END) AS Q3,
SUM(CASE WHEN Q4 = [values].value THEN 1 ELSE 0 END) AS Q4,
SUM(CASE WHEN Q5 = [values].value THEN 1 ELSE 0 END) AS Q5
FROM (
SELECT 0 AS value UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4
) AS [values]
LEFT OUTER JOIN dbo.tblSurvey tn ON --or:: CROSS JOIN dbo.tblSurvey tn
[values].value IN ( Q1, Q2, Q3, Q4, Q5 )
I get this error:
Msg 8120, Level 16, State 1, Line 3
Column 'values.value' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
April 25, 2013 at 12:30 pm
Sorry:
SELECT
[values].value,
SUM(CASE WHEN Q1 = [values].value THEN 1 ELSE 0 END) AS Q1,
SUM(CASE WHEN Q2 = [values].value THEN 1 ELSE 0 END) AS Q2,
SUM(CASE WHEN Q3 = [values].value THEN 1 ELSE 0 END) AS Q3,
SUM(CASE WHEN Q4 = [values].value THEN 1 ELSE 0 END) AS Q4,
SUM(CASE WHEN Q5 = [values].value THEN 1 ELSE 0 END) AS Q5
FROM (
SELECT 0 AS value UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4
) AS [values]
LEFT OUTER JOIN dbo.tblSurvey tn ON --or:: CROSS JOIN dbo.tblSurvey tn
[values].value IN ( Q1, Q2, Q3, Q4, Q5 )
GROUP BY [values].value
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 25, 2013 at 12:31 pm
Just use group by in last.
SELECT
[values].value,
SUM(CASE WHEN Q1 = [values].value THEN 1 ELSE 0 END) AS Q1,
SUM(CASE WHEN Q2 = [values].value THEN 1 ELSE 0 END) AS Q2,
SUM(CASE WHEN Q3 = [values].value THEN 1 ELSE 0 END) AS Q3,
SUM(CASE WHEN Q4 = [values].value THEN 1 ELSE 0 END) AS Q4,
SUM(CASE WHEN Q5 = [values].value THEN 1 ELSE 0 END) AS Q5
FROM (
SELECT 0 AS value UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4
) AS [values]
LEFT OUTER JOIN dbo.[tblSurvey] tn ON --or:: CROSS JOIN dbo.tablename tn
[values].value IN ( Q1, Q2, Q3, Q4, Q5 )
Group by [values].value
April 25, 2013 at 1:39 pm
Thank you all so very much! I can stop beating my head on my desk now!
:-):-D:-P
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply