# Count the rawdata and insert into new table

 0 Dear All, I need help on calculating the counts from the rawdata based on conditions and then I need to insert the values in the summary table. Currently, I have the values stored in rawdata format and I need get the counts in the summary table. Example: Rawdata table ``````Name EmpID Q1 Q2 Q3 John 123 3 2 1 John 123 3 2 1 John 123 2 3 4 John 123 1 3 4 Adam 125 3 5 5 Adam 125 5 4 1 Adam 125 5 2 2 `````` Summary Table ``````Name QID Question Count of (1) Count of (2) Count of (3) Count of (4) Count of (5) John Q1 XXXXXXX 1 1 3 0 0 John Q2 YYYYYYY 0 2 2 0 0 John Q3 AAAAAA 2 0 0 2 0 Adam Q1 XXXXXXX 0 0 1 0 2 Adam Q2 YYYYYYY 0 1 0 1 1 Adam Q3 AAAAAA 1 1 0 0 1 `````` Please let me know the best approach to get this task done. I can give more information if this is not clear. TIA! Cheers, DV more ▼ asked Jun 07, 2010 at 06:45 AM in Default OnlyGraphite 41 ● 6 ● 6 ● 7 Fatherjack ♦♦ 42.4k ● 75 ● 78 ● 108 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 You're starting with a pivoted data set, and you want to pivot it differently - so, you want to unpivot it to a natural set, then re-pivot it to the set you want. This gives you what you want, but it doesn't match your sample, because your sample summary is wrong...``DECLARE @rawData TABLE ([Name] VARCHAR (50), q1 INT, q2 INT, q3 INT);```INSERT INTO @rawData ([Name], [q1], [q2], [q3]) VALUES ('John', 3, 2, 1); INSERT INTO @rawData ([Name], [q1], [q2], [q3]) VALUES ('John', 3, 2, 1); INSERT INTO @rawData ([Name], [q1], [q2], [q3]) VALUES ('John', 2, 3, 4); INSERT INTO @rawData ([Name], [q1], [q2], [q3]) VALUES ('John', 1, 3, 4); INSERT INTO @rawData ([Name], [q1], [q2], [q3]) VALUES ('Adam', 3, 5, 5); INSERT INTO @rawData ([Name], [q1], [q2], [q3]) VALUES ('Adam', 5, 4, 1); INSERT INTO @rawData ([Name], [q1], [q2], [q3]) VALUES ('Adam', 5, 2, 2); SELECT [Name], [QID], [1], [2], [3], [4], [5] FROM ( SELECT [Name], [Marks], [QID] FROM @rawData UNPIVOT (Marks FOR [QID] IN (q1, q2, q3)) AS upv) AS idat PIVOT (COUNT (Marks) FOR Marks IN ([1], [2], [3], [4], [5])) AS pvt ORDER BY [Name], [QID]; ` more ▼ answered Jun 07, 2010 at 07:05 AM Matt Whitfield ♦♦ 29.4k ● 61 ● 65 ● 87 Matt - Thanks a ton!! This works like a charm. I spent one full day trying to get this solved. Once again heartfelt thanks...Cheers - DV Jun 09, 2010 at 05:43 AM OnlyGraphite add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x985
x58

asked: Jun 07, 2010 at 06:45 AM

Seen: 1375 times

Last Updated: Jun 07, 2010 at 06:51 AM