September 21, 2014 at 1:26 am
Hello,
Could some one suggest me the transformation i need to use in SSIS. Please find the excel sheet attached
I want to know how many times Response 1 has occured for Question no.1 and how many times Response 2 has occured for Question no.1 and so on
Thanks in advance
Kavitha
September 21, 2014 at 2:56 am
k.kavitha236 (9/21/2014)
Hello,Could some one suggest me the transformation i need to use in SSIS. Please find the excel sheet attached
I want to know how many times Response 1 has occured for Question no.1 and how many times Response 2 has occured for Question no.1 and so on
Thanks in advance
Kavitha
Quick question, is the data source a file or a database query/table?
Normally I would suggest loading the data into a staging table and do the aggregation in TSQL, mainly to avoid blocking transformations in SSIS (counting/aggregation). Here is a quick example of the TSQL code.
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
;WITH BASE_DATA(Questions,Response) AS
(SELECT * FROM ( VALUES
('Question 1', 3)
,('Question 2', 3)
,('Question 3', 5)
,('Question 4', 5)
,('Question 5', 5)
,('Question 6', 5)
,('Question 7', 2)
,('Question 8', 2)
,('Question 9', 2)
,('Question 10', 4)
,('Question 11', 4)
,('Question 12', 1)
,('Question 13', 1)
,('Question 14', 4)
,('Question 15', 4)
,('Question 16', 4)
,('Question 17', 1)
,('Question 18', 4)
,('Question 19', 3)
,('Question 20', 2)
,('Question 21', 5)
,('Question 22', 4)
,('Question 23', 2)
,('Question 24', 5)
,('Question 25', 1)
,('Question 26', 3)
,('Question 27', 2)
,('Question 28', 3)
,('Question 29', 1)
,('Question 30', 4)
,('Question 31', 2)
,('Question 32', 1)
,('Question 33', 2)
,('Question 34', 3)
,('Question 35', 5)
,('Question 36', 1)
,('Question 37', 2)
,('Question 38', 2)
,('Question 39', 3)
,('Question 40', 4)
,('Question 41', 4)
,('Question 42', 3)
,('Question 43', 2)
,('Question 44', 3)
,('Question 45', 1)
,('Question 46', 5)
,('Question 47', 5)
,('Question 48', 3)
,('Question 49', 3)
,('Question 50', 3)
,('Question 1', 4)
,('Question 2', 1)
,('Question 3', 3)
,('Question 4', 1)
,('Question 5', 3)
,('Question 6', 2)
,('Question 7', 4)
,('Question 8', 5)
,('Question 9', 3)
,('Question 10', 5)
,('Question 11', 1)
,('Question 12', 2)
,('Question 13', 2)
,('Question 14', 1)
,('Question 15', 3)
,('Question 16', 3)
,('Question 17', 3)
,('Question 18', 4)
,('Question 19', 3)
,('Question 20', 2)
,('Question 21', 1)
,('Question 22', 1)
,('Question 23', 4)
,('Question 24', 3)
,('Question 25', 5)
,('Question 26', 5)
,('Question 27', 1)
,('Question 28', 2)
,('Question 29', 2)
,('Question 30', 2)
,('Question 31', 4)
,('Question 32', 4)
,('Question 33', 5)
,('Question 34', 1)
,('Question 35', 2)
,('Question 36', 2)
,('Question 37', 3)
,('Question 38', 3)
,('Question 39', 1)
,('Question 40', 2)
,('Question 41', 2)
,('Question 42', 1)
,('Question 43', 3)
,('Question 44', 4)
,('Question 45', 3)
,('Question 46', 1)
,('Question 47', 5)
,('Question 48', 2)
,('Question 49', 1)
,('Question 50', 2)
,('Question 1', 3)
,('Question 2', 3)
,('Question 3', 2)
,('Question 4', 2)
,('Question 5', 4)
,('Question 6', 2)
,('Question 7', 1)
,('Question 8', 4)
,('Question 9', 4)
,('Question 10', 4)
,('Question 11', 2)
,('Question 12', 1)
,('Question 13', 5)
,('Question 14', 2)
,('Question 15', 5)
,('Question 16', 3)
,('Question 17', 2)
,('Question 18', 1)
,('Question 19', 4)
,('Question 20', 3)
,('Question 21', 3)
,('Question 22', 3)
,('Question 23', 4)
,('Question 24', 2)
,('Question 25', 1)
,('Question 26', 4)
,('Question 27', 5)
,('Question 28', 1)
,('Question 29', 1)
,('Question 30', 5)
,('Question 31', 1)
,('Question 32', 5)
,('Question 33', 3)
,('Question 34', 4)
,('Question 35', 4)
,('Question 36', 4)
,('Question 37', 1)
,('Question 38', 1)
,('Question 39', 4)
,('Question 40', 1)
,('Question 41', 2)
,('Question 42', 3)
,('Question 43', 5)
,('Question 44', 3)
,('Question 45', 5)
,('Question 46', 5)
,('Question 47', 3)
,('Question 48', 5)
,('Question 49', 1)
,('Question 50', 3)
,('Question 1', 4)
,('Question 2', 2)
,('Question 3', 4)
,('Question 4', 2)
,('Question 5', 1)
,('Question 6', 2)
,('Question 7', 3)
,('Question 8', 5)
,('Question 9', 1)
,('Question 10', 1)
,('Question 11', 4)
,('Question 12', 4)
,('Question 13', 3)
,('Question 14', 4)
,('Question 15', 4)
,('Question 16', 2)
,('Question 17', 4)
,('Question 18', 1)
,('Question 19', 4)
,('Question 20', 3)
,('Question 21', 5)
,('Question 22', 5)
,('Question 23', 3)
,('Question 24', 4)
,('Question 25', 1)
,('Question 26', 4)
,('Question 27', 4)
,('Question 28', 5)
,('Question 29', 5)
,('Question 30', 5)
,('Question 31', 4)
,('Question 32', 1)
,('Question 33', 2)
,('Question 34', 4)
,('Question 35', 4)
,('Question 36', 2)
,('Question 37', 5)
,('Question 38', 2)
,('Question 39', 5)
,('Question 40', 3)
,('Question 41', 4)
,('Question 42', 4)
,('Question 43', 3)
,('Question 44', 2)
,('Question 45', 4)
,('Question 46', 3)
,('Question 47', 4)
,('Question 48', 4)
,('Question 49', 2)
,('Question 50', 3)
,('Question 1', 4)
,('Question 2', 5)
,('Question 3', 4)
,('Question 4', 1)
,('Question 5', 4)
,('Question 6', 3)
,('Question 7', 1)
,('Question 8', 4)
,('Question 9', 1)
,('Question 10', 4)
,('Question 11', 2)
,('Question 12', 4)
,('Question 13', 4)
,('Question 14', 1)
,('Question 15', 5)
,('Question 16', 5)
,('Question 17', 5)
,('Question 18', 4)
,('Question 19', 2)
,('Question 20', 4)
,('Question 21', 4)
,('Question 22', 3)
,('Question 23', 3)
,('Question 24', 5)
,('Question 25', 3)
,('Question 26', 3)
,('Question 27', 3)
,('Question 28', 2)
,('Question 29', 3)
,('Question 30', 4)
,('Question 31', 1)
,('Question 32', 1)
,('Question 33', 5)
,('Question 34', 3)
,('Question 35', 3)
,('Question 36', 3)
,('Question 37', 5)
,('Question 38', 2)
,('Question 39', 5)
,('Question 40', 3)
,('Question 41', 2)
,('Question 42', 2)
,('Question 43', 5)
,('Question 44', 1)
,('Question 45', 5)
,('Question 46', 5)
,('Question 47', 5)
,('Question 48', 5)
,('Question 49', 3)
,('Question 50', 5)
) AS X(Questions,Response)
)
,COUNT_RESPONSE_BY_QUESTION AS
(
SELECT
BD.Questions
,BD.Response
,ROW_NUMBER() OVER
(
PARTITION BY BD.Questions
,BD.Response
ORDER BY (SELECT NULL)
) AS QR_RID
,COUNT(BD.Response) OVER
(
PARTITION BY BD.Questions
,BD.Response
) AS QR_CNT
FROM BASE_DATA BD
)
SELECT
CRBQ.Questions
,ISNULL(MAX(CASE WHEN CRBQ.Response = 1 THEN CRBQ.QR_CNT END),0) AS R_1
,ISNULL(MAX(CASE WHEN CRBQ.Response = 2 THEN CRBQ.QR_CNT END),0) AS R_2
,ISNULL(MAX(CASE WHEN CRBQ.Response = 3 THEN CRBQ.QR_CNT END),0) AS R_3
,ISNULL(MAX(CASE WHEN CRBQ.Response = 4 THEN CRBQ.QR_CNT END),0) AS R_4
,ISNULL(MAX(CASE WHEN CRBQ.Response = 5 THEN CRBQ.QR_CNT END),0) AS R_5
FROM COUNT_RESPONSE_BY_QUESTION CRBQ
WHERE CRBQ.QR_RID = 1
GROUP BY CRBQ.Questions;
Results
Questions R_1 R_2 R_3 R_4 R_5
----------- ----------- ----------- ----------- ----------- -----------
Question 1 0 0 2 3 0
Question 10 1 0 0 3 1
Question 11 1 2 0 2 0
Question 12 2 1 0 2 0
Question 13 1 1 1 1 1
Question 14 2 1 0 2 0
Question 15 0 0 1 2 2
Question 16 0 1 2 1 1
Question 17 1 1 1 1 1
Question 18 2 0 0 3 0
Question 19 0 1 2 2 0
Question 2 1 1 2 0 1
Question 20 0 2 2 1 0
Question 21 1 0 1 1 2
Question 22 1 0 2 1 1
Question 23 0 1 2 2 0
Question 24 0 1 1 1 2
Question 25 3 0 1 0 1
Question 26 0 0 2 2 1
Question 27 1 1 1 1 1
Question 28 1 2 1 0 1
Question 29 2 1 1 0 1
Question 3 0 1 1 2 1
Question 30 0 1 0 2 2
Question 31 2 1 0 2 0
Question 32 3 0 0 1 1
Question 33 0 2 1 0 2
Question 34 1 0 2 2 0
Question 35 0 1 1 2 1
Question 36 1 2 1 1 0
Question 37 1 1 1 0 2
Question 38 1 3 1 0 0
Question 39 1 0 1 1 2
Question 4 2 2 0 0 1
Question 40 1 1 2 1 0
Question 41 0 3 0 2 0
Question 42 1 1 2 1 0
Question 43 0 1 2 0 2
Question 44 1 1 2 1 0
Question 45 1 0 1 1 2
Question 46 1 0 1 0 3
Question 47 0 0 1 1 3
Question 48 0 1 1 1 2
Question 49 2 1 2 0 0
Question 5 1 0 1 2 1
Question 50 0 1 3 0 1
Question 6 0 3 1 0 1
Question 7 2 1 1 1 0
Question 8 0 1 0 2 2
Question 9 2 1 1 1 0
September 21, 2014 at 7:35 am
Hello Eirikur Eiriksson,
Thank you for the reply.I will try it. Yes it is a datasource.
Regards
Kavitha
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply