Rows as columns in select and counting

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Rows as columns in select and counting

Author

Topic

g.c.benjamin
Starting Member

10 Posts

Posted - 2013-09-20 : 03:01:31

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-20 : 05:41:03

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

--
Chandu

scottdrake
Starting Member

1 Post

Posted - 2013-09-20 : 09:26:32

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

g.c.benjamin
Starting Member

10 Posts

Posted - 2013-09-20 : 20:27:25

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!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 03:46:32

[code]

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

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

g.c.benjamin
Starting Member

10 Posts

Posted - 2013-09-24 : 20:28:36

Thanks Visakh, Just what I was after

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources