SQL Server query to generate custom report based on table data

Question

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

JamieD77 · Accepted Answer · 2017-10-03 20:52:46Z

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]

adelsam · Accepted Answer · 2017-10-03 20:47:22Z

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.

Collectives™ on Stack Overflow

SQL Server query to generate custom report based on table data

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related