TSql Script to output records with all possible column values

Hello Everyone,

In this post I would like to focus on a tsql script to select data from a table with unique account ID for accounts but different values for the other columns and the query should be able to select data only if certain column values exist. Lets take an example to give you a clear picture. I have a table whose DDL is:

CREATE TABLE [dbo].[AccountData]([AccName] [nvarchar](255) NULL,[AccID] [float] NULL,[Duration] [float] NULL,[Issue] [nvarchar](255) NULL) ON [PRIMARY]

and the resultset is as shown:

Now I want to select only those records where Issue column has values Phase1,Phase2,Phase3 and Phase4. So if I use the following query it returns me 0 results.

select * from accountdata where issue='Phase1' and issue='Phase2' and issue='Phase3' and issue='Phase4'

I want to get accounts test1 and test4 in the result with all the columns and records. Here is the script for that:

Select a.AccName, a.AccID, a.Duration, a.Issue From dbo.AccountData a Where a.AccName In (Select a1.AccName From dbo.AccountData a1 Where a1.Issue In ('Phase1', 'Phase2', 'Phase3', 'Phase4) Group By a1.AccName Having Count(Distinct a1.Issue) = 4)

Here is the expected result set.


Hope this helps!

This entry was posted in SQL Server DBA Stuff and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s