November 9, 2009 at 12:16 pm
Hi Guys,
I need some help to come up with one query from following set of Data (Table 1), the quey should return data in the format of Table 2
this is source of data table (Table 1)
-------------------------------------------------- ----------- -----------
TestName FailedCount Testtype
-------------------------------------------------- ----------- -----------
Code 0 W1
Bank_Name 0 W2
Routing_Number 0 W3
Routing_Number 0 E4
Account_Number 0 W5
Address2 2961 W6
Address2 0 W7
Address1 0 W8
Employer_Number 0 E9
Employer_Number 0 W10
I need data in this following format (table 2) from the source data Table1
Tests E4 E9 W1 W2 W3 W5 W6 W7 W8 W10
Code 0
Bank_Name 0
Routing_Number 0 0
Account_Number 0
Address2 29610
Address1 0
Employer_Number 0 0
Any kind of help would be really appreciated
Thanks in advance
Jigsh
November 9, 2009 at 3:52 pm
HI,
not sure if this is what you are after but try with pivot table:
SELECT testname, [E4],[E9],[W1],[W10],[W2],[W3],[W5],[W6],[W7],[W8]
FROM
(SELECT testname, FailedCount, Testtype
FROM testtable) p
PIVOT
(
SUM (FailedCount)
FOR Testtype IN
( [E4],[E9],[W1],[W10],[W2],[W3],[W5],[W6],[W7],[W8] )
) AS pvt
order by testname
November 9, 2009 at 4:02 pm
See the bottom 2 articles referenced in my signature for an explanation of how to do it. Here is some code that should work:
IF OBJECT_ID(N'temp_tests', N'U') IS NOT NULL
BEGIN
DROP TABLE temp_tests
END
CREATE TABLE temp_tests
(
test_Name VARCHAR(25),
failed_count int,
test_type VARCHAR(5)
) ;
DECLARE
@sql1 NVARCHAR(MAX),
@sql2 NVARCHAR(MAX),
@sql3 NVARCHAR(MAX),
@sql_exec NVARCHAR(MAX)
INSERT INTO
temp_tests
(
test_Name,
failed_count,
test_type
)
SELECT
'Code',
0,
'W1'
UNION ALL
Select
'Bank_Name',
0,
'W2'
UNION ALL
Select
'Routing_Number',
0,
'W3'
UNION ALL
Select
'Routing_Number',
0,
'E4'
UNION ALL
Select
'Account_Number',
0,
'W5'
UNION ALL
Select
'Address2',
2961,
'W6'
UNION ALL
Select
'Address2',
0,
'W7'
UNION ALL
Select
'Address1',
0,
'W8'
UNION ALL
Select
'Employer_Number',
0,
'E9'
UNION ALL
Select
'Employer_Number',
0,
'W10'
SET @sql1 = N'Select Test_Name as tests, '
SELECT
@sql2 = COALESCE(@sql2, N'') + N'Min(Case when ' + QUOTENAME(test_type, '''') +
N'= test_type then failed_count else Null end) as ' + test_type + N',' + NCHAR(10)
FROM
(
SELECT DISTINCT
test_type
From
temp_tests
) AS a
SET @sql3 = N' from
(
Select
test_name,
test_type,
min(failed_count) as failed_count
From
temp_tests
group by test_name, test_type
) as A Group By test_name '
SET @sql_exec = @sql1 + substring(@sql2, 1, LEN(@sql2) - 2) + @sql3
SELECT
@sql_exec
EXEC (@sql_exec)
It doesn't give you any ordering for either the columns or rows, but does return the correct data as per your test data. You can replace the NULL with an empty string if you want that removed.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply