SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Rows as columns in select and counting
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

g.c.benjamin
Starting Member

10 Posts

Posted - 09/20/2013 :  03:01:31  Show Profile  Reply with Quote
Hi, having some trouble trying to work out a query. I've already implemented this in code but would really like to do a single query for reporting purposes.

Heres a cut down version of the tables:


CREATE TABLE #Forms
		(
			[Id] int NOT NULL IDENTITY(1, 1),
			[DateValidFrom] datetime NOT NULL,
			[DateValidTo] datetime NOT NULL
		)
		
INSERT INTO #Forms (DateValidFrom, DateValidTo) VALUES ('2013-01-01', '2014-01-01')
		
CREATE TABLE #Questions
		(
			[Id] int NOT NULL IDENTITY(1, 1),
			[QuestionText] nvarchar(999) NOT NULL
		)
		
INSERT INTO #Questions (QuestionText) VALUES ('Question 1?')
INSERT INTO #Questions (QuestionText) VALUES ('Question 2?')
INSERT INTO #Questions (QuestionText) VALUES ('Question 3?')
INSERT INTO #Questions (QuestionText) VALUES ('Question 4?')
INSERT INTO #Questions (QuestionText) VALUES ('Question 5?')
INSERT INTO #Questions (QuestionText) VALUES ('Question 6?')
		
CREATE TABLE #Headings
		(
			[Id] int NOT NULL IDENTITY(1, 1),
			[HeadingText] nvarchar(100) NOT NULL
		)
		
INSERT INTO #Headings (HeadingText) VALUES ('Heading 1?')
INSERT INTO #Headings (HeadingText) VALUES ('Heading 2?')
INSERT INTO #Headings (HeadingText) VALUES ('Heading 3?')
		
CREATE TABLE #FormQuestions
		(
			[Id] int NOT NULL IDENTITY(1, 1),
			[QuestionId] int NOT NULL,
            [FormId] int NOT NULL,
            [HeadingId] int NOT NULL,
			[QuestionSortOrder] int NOT NULL,
            [HeadingSortOrder] int NOT NULL
		)
		
INSERT INTO #FormQuestions (QuestionId, FormId, HeadingId, QuestionSortOrder, HeadingSortOrder) VALUES (1, 1, 1, 1, 1)
INSERT INTO #FormQuestions (QuestionId, FormId, HeadingId, QuestionSortOrder, HeadingSortOrder) VALUES (2, 1, 1, 2, 1)
INSERT INTO #FormQuestions (QuestionId, FormId, HeadingId, QuestionSortOrder, HeadingSortOrder) VALUES (3, 1, 2, 1, 2)
INSERT INTO #FormQuestions (QuestionId, FormId, HeadingId, QuestionSortOrder, HeadingSortOrder) VALUES (4, 1, 2, 2, 2)
INSERT INTO #FormQuestions (QuestionId, FormId, HeadingId, QuestionSortOrder, HeadingSortOrder) VALUES (5, 1, 3, 1, 3)
INSERT INTO #FormQuestions (QuestionId, FormId, HeadingId, QuestionSortOrder, HeadingSortOrder) VALUES (6, 1, 3, 2, 3)

CREATE TABLE #FormInstance
		(
			[Id] int NOT NULL IDENTITY(1, 1),
            [PersonName] nvarchar(50) NOT NULL,
            [PersonEmail] nvarchar(75) NOT NULL,
            [TimeSubmitted] datetime NOT NULL
		)
		
INSERT INTO #FormInstance (PersonName, PersonEmail, TimeSubmitted) VALUES ('Jack', 'jack@email.com', '2013-02-02')
INSERT INTO #FormInstance (PersonName, PersonEmail, TimeSubmitted) VALUES ('Jill', 'jill@email.com', '2013-02-03')
		
CREATE TABLE #InstanceQuestion
		(
			[Id] int NOT NULL IDENTITY(1, 1),
            [FormInstanceId] int NOT NULL,
            [FormQuestionId] int NOT NULL,
            [Response] nvarchar(4000)
		)
		
INSERT INTO #InstanceQuestion (FormInstanceId, FormQuestionId, Response) VALUES (1, 1, 'blah blah blah')
INSERT INTO #InstanceQuestion (FormInstanceId, FormQuestionId, Response) VALUES (1, 2, 'blah blah blah')
INSERT INTO #InstanceQuestion (FormInstanceId, FormQuestionId, Response) VALUES (1, 3, NULL)
INSERT INTO #InstanceQuestion (FormInstanceId, FormQuestionId, Response) VALUES (1, 4, NULL)
INSERT INTO #InstanceQuestion (FormInstanceId, FormQuestionId, Response) VALUES (1, 5, 'blah blah blah')
INSERT INTO #InstanceQuestion (FormInstanceId, FormQuestionId, Response) VALUES (1, 6, NULL)
INSERT INTO #InstanceQuestion (FormInstanceId, FormQuestionId, Response) VALUES (2, 1, NULL)
INSERT INTO #InstanceQuestion (FormInstanceId, FormQuestionId, Response) VALUES (2, 2, 'blah blah blah')
INSERT INTO #InstanceQuestion (FormInstanceId, FormQuestionId, Response) VALUES (2, 3, 'blah blah blah')
INSERT INTO #InstanceQuestion (FormInstanceId, FormQuestionId, Response) VALUES (2, 4, 'blah blah blah')
INSERT INTO #InstanceQuestion (FormInstanceId, FormQuestionId, Response) VALUES (2, 5, NULL)
INSERT INTO #InstanceQuestion (FormInstanceId, FormQuestionId, Response) VALUES (2, 6, NULL)


This data structure is for the creation of custom forms. Admin users add questions and headings, then create a form and add questions and headings to the form. So the Forms table is a form and the FormQuestions hold the headings and questions for that form. Then once a user fills out a form, a FormInstance is created and the answers are stored in the InstanceQuestions. So in the above code to create the tables, one form has been created. The form has 3 headings that contain 2 questions under each heading. So the form has 3 headings and 6 questions.
2 Users have filled out a form, Jack and Jill so there are 2 FormInstances. And their answers are stored in the InstanceQuestion table, so 2 forms filled out, 6 questions a form so 12 InstanceQuestions.

So, now on to the report. I want to report on responses in the InstanceQuestion table that are not null. I want to count each not null response by heading. And the form headings as columns. So for example the report would show that jack filled out a form and for heading 1 (as a column) there were 2 not null responses (in the row), for heading 2 (column) there were 0 not null responses (row) and so on (join InstanceQuestions with FormQuestions to get the Heading).

Is this possible with SQL? If there is anything that needs to be made clearer, let me know.

Thanks,
Gareth

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 09/20/2013 :  05:41:03  Show Profile  Reply with Quote
To convert rows as columns we can use PIVOT in SQL Server..
Can you provide expected output for the above sample data

--
Chandu
Go to Top of Page

scottdrake
Starting Member

USA
1 Posts

Posted - 09/20/2013 :  09:26:32  Show Profile  Reply with Quote
There are a couple of ways to pivot data in SQL. The old way is better if you need to pivot multiple columns. Here are a couple of articles I wrote about it:

http://topsql.com/post/How-to-Pivot-Data-in-SQL-Server-Old-School-Style
http://topsql.com/post/How-to-Pivot-Data-in-SQL-Server-New-School-Style
Go to Top of Page

g.c.benjamin
Starting Member

10 Posts

Posted - 09/20/2013 :  20:27:25  Show Profile  Reply with Quote
quote:
Originally posted by bandi

To convert rows as columns we can use PIVOT in SQL Server..
Can you provide expected output for the above sample data

--
Chandu



Hi Bandi,

I'm trying to get something like this, so counting which questions under each heading are not null.


PersonName TimeSubmitted Heading1 Heading2 Heading3
Jack       2/02/2013	 2	  0	   1
Jill       3/02/2013	 1	  2	   0



I'm aware of pivot and tried a few different ways but still couldn't get it to work the way I wanted!

Edited by - g.c.benjamin on 09/20/2013 20:28:16
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/22/2013 :  03:46:32  Show Profile  Reply with Quote



SELECT fi.PersonName,fi.TimeSubmitted,h.HeadingText,
COUNT(iq.Response)AS cnt
INTO ##temp
FROM #InstanceQuestion iq
INNER JOIN #FormInstance fi
ON fi.Id = iq.FormInstanceId
INNER JOIN #FormQuestions fq
ON fq.QuestionId = iq.FormQuestionId
INNER JOIN #Headings h
ON h.Id = fq.HeadingId
GROUP BY fi.PersonName,fi.TimeSubmitted,h.HeadingText


DECLARE @HeaderList varchar(max)
SELECT @HeaderList=STUFF((SELECT DISTINCT ',[' +HeadingText + ']'
        FROM ##temp
        FOR XML PATH('')),1,1,'')

DECLARE @DSQL varchar(max) ='SELECT *
FROM ##temp
PIVOT (SUM(cnt) FOR HeadingText IN (' + @headerList + '))P'
exec (@DSQL)
        

output
----------------------------------------------------------------------------
PersonName	TimeSubmitted	Heading 1?	Heading 2?	Heading 3?
----------------------------------------------------------------------------
Jack	2013-02-02 00:00:00.000	2	0	1
Jill	2013-02-03 00:00:00.000	1	2	0



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

g.c.benjamin
Starting Member

10 Posts

Posted - 09/24/2013 :  20:28:36  Show Profile  Reply with Quote
Thanks Visakh, Just what I was after
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000