CODEWITHSUNDEEP
sql-server
Raghavendra
Raghavendra

Reputation: 3998

SQL Server query to generate custom report based on table data

I am using SQL Server database which has tables x.

Table: x

x_id    Type        Text        ID
-------------------------------------------
| 1  |  Type1  |    txt1   |    id1
| 2  |  Type2  |    txt2   |    id2
| 3  |  Type3  |    txt3   |    id1
| 4  |  Type3  |    txt4   |    id3

Here, Each ID can have different texts for Type1, Type2, Type 3 ... etc.

I need to generate report which has fields ID, Type1_exists, Type2_exists, Type3_exists, Type1_Text, Type2_Text, Type3_Text where exists columns should say whether ID has that text or not (Y/N). If ID has record which has Type as "Type1" then Type1_exists value should be "Y" otherwise "N".

So, Sample result that I am expecting is 

ID    Type1_Exists  Type1_Text   Type2_Exists  Type2_Text  Type3_Exists Type3_Text
---------------------------------------------------------------------
| id1  |      Y     | txt1   |        N        |      |     Y           |  txt3
| id2  |      N     |        |        Y        | txt2 |     N           |  
| id3  |      N     |        |        N        |      |     Y           |  txt4

Upvotes: 0

Views: 45

Answers (2)

JamieD77
JamieD77

Reputation: 13949

You can use PIVOT or conditional aggregation

PIVOT:

SELECT 
    [ID],
    [Type1_Exists] = CASE WHEN [Type1] IS NULL THEN 'N' ELSE 'Y' END,
    [Type1_Text] = [Type1],
    [Type2_Exists] = CASE WHEN [Type2] IS NULL THEN 'N' ELSE 'Y' END,
    [Type2_Text] = [Type2],
    [Type3_Exists] = CASE WHEN [Type3] IS NULL THEN 'N' ELSE 'Y' END,
    [Type3_Text] = [Type3] 

FROM (
    SELECT [ID], [Type], [Text]
    FROM x 
) t 
PIVOT (
    MAX([Text])
    FOR [Type] IN ([Type1],[Type2],[Type3])
) p

CONDITIONAL AGGREGATION:

SELECT  
    [ID],
    MAX(CASE WHEN [Type] = 'Type1' THEN 'Y' ELSE 'N' END) AS [Type1_Exists],
    MAX(CASE WHEN [Type] = 'Type1' THEN [Text] END) AS [Type1_Text],
    MAX(CASE WHEN [Type] = 'Type2' THEN 'Y' ELSE 'N' END) AS [Type2_Exists],
    MAX(CASE WHEN [Type] = 'Type2' THEN [Text] END) AS [Type2_Text],
    MAX(CASE WHEN [Type] = 'Type3' THEN 'Y' ELSE 'N' END) AS [Type3_Exists],
    MAX(CASE WHEN [Type] = 'Type3' THEN [Text] END) AS [Type3_Text]
FROM 
    x 
GROUP BY [ID]

Upvotes: 2

adelsam
adelsam

Reputation: 1

One way to do it is to join the table against itself, with restrictions in the ON clause to pull rows for each type as follows:

select distinct x0.ID, 
case when x1.Text is null then 'N' else 'Y' end type1_exists,
x1.Text type1_text, 
case when x2.Text is null then 'N' else 'Y' end type2_exists,
x2.Text type2_text,
case when x3.Text is null then 'N' else 'Y' end type3_exists,
x3.Text type3_text, 
case when x4.Text is null then 'N' else 'Y' end type4_exists,
x4.Text type4_text
from x x0 left join x x1 on x0.ID = x1.ID and x1.Type = 'Type1'
left join x x2 on x0.ID = x2.ID and x2.Type = 'Type2'
left join x x3 on x0.ID = x3.ID and x3.Type = 'Type3'
left join x x4 on x0.ID = x4.ID and x4.Type = 'Type4'
order by 1;

SQL Server also has explicit support for crosstab queries using PIVOT.

Upvotes: 0

Related Questions