how do i summarise and transpose table data

Question

Post reply

how do i summarise and transpose table data

Fatherjack

SSCommitted

Points: 1575
More actions
January 19, 2009 at 10:21 am

#128057

Hi,
I have a table as:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[QuestionResponses](
[Q1] [float] NULL,
[Q2] [float] NULL,
[Q3] [float] NULL,
[Q4] [float] NULL,
[Q5] [float] NULL,
[Q6] [float] NULL,
[Q7] [float] NULL,
[Q8] [float] NULL,
[Q9] [float] NULL,
[Q10] [float] NULL,
[Q11] [float] NULL,
[Q12] [float] NULL,
[Q13] [float] NULL,
[Q14] [float] NULL,
[Q15] [float] NULL,
[Q16] [float] NULL,
[Q17] [float] NULL,
[Q18] [float] NULL,
[Q19] [float] NULL,
[Q20] [float] NULL,
[Q21] [float] NULL,
[Q22] [float] NULL,
[Q23] [float] NULL,
[Q24] [float] NULL,
[Q25] [float] NULL,
[Q26] [float] NULL,
[Q27] [float] NULL,
[Q28] [float] NULL,
[Q29] [float] NULL,
[Q30] [float] NULL,
[Q31] [float] NULL,
[Q32] [float] NULL,
[Q33] [float] NULL,
[Q34] [float] NULL,
[Q35] [float] NULL,
[Q36] [float] NULL,
[Q37] [float] NULL,
[Q38] [float] NULL,
[Q39] [float] NULL,
[Q40] [float] NULL,
[Q41] [float] NULL,
[Q42] [float] NULL,
[Q43] [float] NULL,
[Q44] [float] NULL,
[Q45] [float] NULL,
[Q46] [float] NULL,
[Q47] [float] NULL,
[Q48] [float] NULL,
[Q49] [float] NULL,
[Q50] [float] NULL,
[Q51] [float] NULL,
[Q52] [float] NULL
) ON [PRIMARY]
where any record has the value NULL, 0 or 1 for any column. It relates to their answer to the question. IE if question 20 was answered with a YES then Q20 has a 1 in the record.
I have been asked to report on the data in the form
YES NO Not Known
Q1 [count] [count] [count]
Q2 [count] [count] [count]
Q3 [count] [count] [count]
Q4 [count] [count] [count]
... [count] [count] [count]
I have tried using PIVOT and CUBE etc but cannot see how I can get all of the column names into rows with the count of the NULL, 0 or 1 values in this layout.
any advice gratefully received.

Viewing 8 posts - 1 through 8 (of 8 total)

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

See the link in my signature for Jeff Moden's article on Cross-Tabs. Should solve this handily.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Fatherjack SSCommitted Points: 1575 More actions · Answer 2

Sorry to be a pain but I have read through several PIVOT articles (BOL etc) but cant see how that will help where I need to logically decide between Null,0 and 1 before I count the record value for each column.

Could you possibly post a little code along the lines of your suggestion please?

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

Assuming that this is your actual table design, there are some major flaws in your table design.

1. It is not in any of the defined normal forms. What if the no. of questions are increased?

2. You said it can only the values 0, 1 OR NULL. So, why'd you declared that column as float? Can't be converted to bit or char data types?

You surely have to rethink about the design. And probably, creating a table to hold all the questions and another table to hold responses against each question.

Now, in case, you don't bother to change the design or you don't have the religion to do so or some whatever reason, then here is what that could just do the same...

Actually, you doesn't need to do a PIVOT, and instead you need the exact opposite of it, i.e. UNPIVOT...

IF ( OBJECT_ID( 'tempdb..#QuestionResponses' ) IS NOT NULL )

DROP TABLE #QuestionResponses

CREATE TABLE #QuestionResponses

(

[Q1] [float] NULL,

[Q2] [float] NULL,

[Q3] [float] NULL,

[Q4] [float] NULL,

[Q5] [float] NULL

)

INSERT#QuestionResponses( Q1, Q2, Q3, Q4, Q5 )

SELECT1, 0, 1, NULL, NULL

UNION ALL

SELECTNULL, 0, 0, 1, 1

SELECTQuestion,

SUM( CASE WHEN Response = 1 THEN 1 ELSE 0 END ) AS YesResponse,

SUM( CASE WHEN Response = 0 THEN 1 ELSE 0 END ) AS NoResponse,

SUM( CASE WHEN Response = 2 THEN 1 ELSE 0 END ) AS UnknownResponse

FROM(

SELECTCOALESCE( Q1, 2 ) AS Q1, COALESCE( Q2, 2 ) AS Q2,

COALESCE( Q3, 2 ) AS Q3, COALESCE( Q4, 2 ) AS Q4,

COALESCE( Q5, 2 ) AS Q5

FROM#QuestionResponses

) QP

UNPIVOT

(

Response FOR Question IN( [Q1], [Q2], [Q3], [Q4], [Q5] )

) P

GROUP BY Question

IF ( OBJECT_ID( 'tempdb..#QuestionResponses' ) IS NOT NULL )

DROP TABLE #QuestionResponses

--Ramesh

Fatherjack SSCommitted Points: 1575 More actions · Answer 4

Ramesh,

firstly thanks very much for the code, I have converted it to fit our table and it works perfectly. I hadnt seen the problem as something that UNPIVOT would fix.

With regard to the table; I didnt design or install the database, yes I would love to redesign the database and have seperate tables, different data types, etc etc. to allow for much simpler queries and development/maintenance. However that would mean a redesign of the application also and we simply dont have the time/man power to carry out that much work. It isnt what I would have created but it is what I am expected to maintain...

Thanks again

Jonathan

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

Thanks for the kind words:), though I would be much happier if that can be fixed at the base itself.

--Ramesh

Garadin One Orange Chip Points: 29613 More actions · Answer 6

Yeah, definitely read that one too quick. Sorry for the bad answer Jon.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Fatherjack SSCommitted Points: 1575 More actions · Answer 7

no worries!!

did have a wry smile about maybe creating a How To Post Answers article!

immediate problem solved, just need to get some time to schedule in changes to DB to make it more sustainable.

cheers

Jonathan