January 24, 2013 at 10:18 am
I'm trying to come up with a fairly simple way to query three different columns in one table such that if ANY row within the column has the value 'Y', it returns a single row with a literal value (the column name) and either 'Y' or 'N'.
For example, if I have a table with 50 rows and 3 columns, and the column values may be NULL, 'Y', 'N' (or really any value), I want the result to look like:
'ColumnName1' 'Y'
'ColumnName2' 'N'
'ColumnName3' 'Y'
assuming there are no 'Y' values in any row of ColumnName2.
Preferably, the solution does not involve going after the data in the table 3 separate times, but if no other way...
Thanks for any help you can provide.
January 24, 2013 at 11:56 am
Try searching for dwainc's article CROSS APPLY VALUES, it's the simplest solution for this. If you can't find it, post up ddl and dml for your 50-row table. The link in my sig will show you how to do this.
The solution to this is far simpler - and more flexible - than you may think.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 24, 2013 at 7:03 pm
ChrisM@home (1/24/2013)
Try searching for dwainc's article CROSS APPLY VALUES, it's the simplest solution for this. If you can't find it, post up ddl and dml for your 50-row table. The link in my sig will show you how to do this.The solution to this is far simpler - and more flexible - than you may think.
As much as I like tooting my own horn, that won't work in SQL 2005.
You'll need to do a UNION ALL with the 3 columns. With DDL and sample data I'm sure either ChrisM or I could show you how.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 25, 2013 at 5:26 am
Thanks for your quick responses.
A quick overview: I need to create a daily report (for our 1000's of customers) that shows the values of various settings within our software. One table (of many) is:
MisIcg_InteractionChecks (PK = *)
*SourceID varchar no 3 no no no SQL_Latin1_General_CP1_CS_AS
*MisIcgID varchar no 30 no no no SQL_Latin1_General_CP1_CS_AS
*InteractionID varchar no 25 no no no SQL_Latin1_General_CP1_CS_AS
RowUpdateDateTime datetime no 8 yes (n/a) (n/a) NULL
InteractionCheckMedication varchar no 2 yes no yes SQL_Latin1_General_CP1_CS_AS
InteractionCheckComponent varchar no 2 yes no yes SQL_Latin1_General_CP1_CS_AS
InteractionCheckIntravenous varchar no 2 yes no yes SQL_Latin1_General_CP1_CS_AS
InteractionCheckSeverity varchar no 38 yes no yes SQL_Latin1_General_CP1_CS_AS
Sample data:
DEM ALL CondCont 2013-01-24 14:07:50.000 Y Y N Severe,Intermediate,Mild
DEM ALL Drug 2013-01-24 14:07:50.000 Y Y N Severe,Intermediate,Mild
DEM ALL DupClass 2013-01-24 14:07:50.000 N Y N NULL
DEM ALL DupGen 2013-01-24 14:07:50.000 N N N NULL
DEM ALL DupIng 2013-01-24 14:07:50.000 Y N N NULL
DEM ALL FoodInt 2013-01-24 14:07:50.000 Y Y NULL Severe
What I'm hoping to do is output the column name (or a literal value of its name) and if ANY row within that column has a specified value (perhaps 'Y') then output that value, else another value:
Column Value
InteractionCheckMedication Y
InteractionCheckComponent Y
InteractionCheckIntravenous N
...
As some tables have a fairly large number of columns, I'm also hoping to query the table once to get the values rather than one column at a time (prefer not to have a long list of SELECT ...UNION ALL SELECT ... ).
January 25, 2013 at 6:18 am
Have a look at the article linked in my sig (please read this) for ddl and dml recipes. This will be much easier if folks have readily-consumable data to work with.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 25, 2013 at 7:22 am
Hope this works for you; generated using Generate Scripts feature:
CREATE TABLE [dbo].[MisIcg_InteractionChecks](
[SourceID] [varchar](3) NOT NULL,
[MisIcgID] [varchar](30) NOT NULL,
[InteractionID] [varchar](25) NOT NULL,
[RowUpdateDateTime] [datetime] NULL,
[InteractionCheckMedication] [varchar](2) NULL,
[InteractionCheckComponent] [varchar](2) NULL,
[InteractionCheckIntravenous] [varchar](2) NULL,
[InteractionCheckSeverity] [varchar](38) NULL,
CONSTRAINT MisIcg_InteractionChecks_PK PRIMARY KEY CLUSTERED
(
[SourceID] ASC,
[MisIcgID] ASC,
[InteractionID] ASC
)
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ALL', N'CondCont', CAST(0x0000A15000E8DD48 AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ALL', N'Drug', CAST(0x0000A15000E8DD48 AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ALL', N'DupClass', CAST(0x0000A15000E8DD48 AS DateTime), N'N', N'Y', N'Y', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ALL', N'DupGen', CAST(0x0000A15000E8DD48 AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ALL', N'DupIng', CAST(0x0000A15000E8DD48 AS DateTime), N'Y', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ALL', N'FoodInt', CAST(0x0000A15000E8DD48 AS DateTime), N'Y', N'Y', N'Y', N'Severe')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ARRA', N'CondCont', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ARRA', N'Drug', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ARRA', N'DupClass', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'Y', N'Y', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ARRA', N'DupGen', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ARRA', N'DupIng', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ARRA', N'FoodInt', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ARRANOCX', N'CondCont', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ARRANOCX', N'Drug', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ARRANOCX', N'DupClass', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ARRANOCX', N'DupGen', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ARRANOCX', N'DupIng', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ARRANOCX', N'FoodInt', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'EDM CONFLICT GROUP', N'CondCont', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'EDM CONFLICT GROUP', N'Drug', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'EDM CONFLICT GROUP', N'DupClass', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'EDM CONFLICT GROUP', N'DupGen', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'EDM CONFLICT GROUP', N'DupIng', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'EDM CONFLICT GROUP', N'FoodInt', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, N'Severe')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ICG', N'CondCont', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ICG', N'Drug', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ICG', N'DupClass', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', NULL, NULL, NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ICG', N'DupGen', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ICG', N'DupIng', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'ICG', N'FoodInt', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'OM.DOC', N'CondCont', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'OM.DOC', N'Drug', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'N', N'Y', N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'OM.DOC', N'DupClass', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'OM.DOC', N'DupGen', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'OM.DOC', N'DupIng', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'OM.DOC', N'FoodInt', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PARKER', N'CondCont', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PARKER', N'Drug', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PARKER', N'DupClass', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PARKER', N'DupGen', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PARKER', N'DupIng', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PARKER', N'FoodInt', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PCC', N'CondCont', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', NULL, NULL, N'Severe,Intermediate')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PCC', N'Drug', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', NULL, NULL, N'Severe,Intermediate')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PCC', N'DupClass', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', NULL, NULL, NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PCC', N'DupGen', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', NULL, NULL, NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PCC', N'DupIng', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', NULL, NULL, NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PCC', N'FoodInt', CAST(0x0000A15000E8E0CC AS DateTime), N'N', NULL, NULL, NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PCM', N'CondCont', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PCM', N'Drug', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PCM', N'DupClass', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'Y', N'Y', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PCM', N'DupGen', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PCM', N'DupIng', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'PCM', N'FoodInt', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'TC.UK', N'CondCont', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'TC.UK', N'Drug', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild')
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'TC.UK', N'DupClass', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'Y', N'Y', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'TC.UK', N'DupGen', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'TC.UK', N'DupIng', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'N', N'N', NULL)
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity]) VALUES (N'DEM', N'TC.UK', N'FoodInt', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe')
GO
January 25, 2013 at 8:17 am
Thanks for the sample data. Try this:
-- Sample data
DROP TABLE [dbo].[MisIcg_InteractionChecks]
CREATE TABLE [dbo].[MisIcg_InteractionChecks](
[SourceID] [varchar](3) NOT NULL,
[MisIcgID] [varchar](30) NOT NULL,
[InteractionID] [varchar](25) NOT NULL,
[RowUpdateDateTime] [datetime] NULL,
[InteractionCheckMedication] [varchar](2) NULL,
[InteractionCheckComponent] [varchar](2) NULL,
[InteractionCheckIntravenous] [varchar](2) NULL,
[InteractionCheckSeverity] [varchar](38) NULL,
CONSTRAINT MisIcg_InteractionChecks_PK PRIMARY KEY CLUSTERED
(
[SourceID] ASC,
[MisIcgID] ASC,
[InteractionID] ASC
)
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[MisIcg_InteractionChecks] ([SourceID], [MisIcgID], [InteractionID], [RowUpdateDateTime], [InteractionCheckMedication], [InteractionCheckComponent], [InteractionCheckIntravenous], [InteractionCheckSeverity])
VALUES
(N'DEM', N'ALL', N'CondCont', CAST(0x0000A15000E8DD48 AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild'),
(N'DEM', N'ALL', N'Drug', CAST(0x0000A15000E8DD48 AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild'),
(N'DEM', N'ALL', N'DupClass', CAST(0x0000A15000E8DD48 AS DateTime), N'N', N'Y', N'Y', NULL),
(N'DEM', N'ALL', N'DupGen', CAST(0x0000A15000E8DD48 AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'ALL', N'DupIng', CAST(0x0000A15000E8DD48 AS DateTime), N'Y', N'N', N'N', NULL),
(N'DEM', N'ALL', N'FoodInt', CAST(0x0000A15000E8DD48 AS DateTime), N'Y', N'Y', N'Y', N'Severe'),
(N'DEM', N'ARRA', N'CondCont', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild'),
(N'DEM', N'ARRA', N'Drug', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild'),
(N'DEM', N'ARRA', N'DupClass', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'Y', N'Y', NULL),
(N'DEM', N'ARRA', N'DupGen', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'ARRA', N'DupIng', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'N', N'N', NULL),
(N'DEM', N'ARRA', N'FoodInt', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild'),
(N'DEM', N'ARRANOCX', N'CondCont', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'ARRANOCX', N'Drug', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'ARRANOCX', N'DupClass', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'ARRANOCX', N'DupGen', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'ARRANOCX', N'DupIng', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'ARRANOCX', N'FoodInt', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'EDM CONFLICT GROUP', N'CondCont', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, N'Severe,Intermediate,Mild'),
(N'DEM', N'EDM CONFLICT GROUP', N'Drug', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, N'Severe,Intermediate,Mild'),
(N'DEM', N'EDM CONFLICT GROUP', N'DupClass', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, NULL),
(N'DEM', N'EDM CONFLICT GROUP', N'DupGen', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'N', N'N', NULL),
(N'DEM', N'EDM CONFLICT GROUP', N'DupIng', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'N', N'N', NULL),
(N'DEM', N'EDM CONFLICT GROUP', N'FoodInt', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, N'Severe'),
(N'DEM', N'ICG', N'CondCont', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, N'Severe,Intermediate,Mild'),
(N'DEM', N'ICG', N'Drug', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, N'Severe,Intermediate,Mild'),
(N'DEM', N'ICG', N'DupClass', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', NULL, NULL, NULL),
(N'DEM', N'ICG', N'DupGen', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, NULL),
(N'DEM', N'ICG', N'DupIng', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, NULL),
(N'DEM', N'ICG', N'FoodInt', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', NULL, NULL, N'Severe,Intermediate,Mild'),
(N'DEM', N'OM.DOC', N'CondCont', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild'),
(N'DEM', N'OM.DOC', N'Drug', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'N', N'Y', N'Severe,Intermediate,Mild'),
(N'DEM', N'OM.DOC', N'DupClass', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'OM.DOC', N'DupGen', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'N', N'N', NULL),
(N'DEM', N'OM.DOC', N'DupIng', CAST(0x0000A15000E8DFA0 AS DateTime), N'Y', N'N', N'N', NULL),
(N'DEM', N'OM.DOC', N'FoodInt', CAST(0x0000A15000E8DFA0 AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'PARKER', N'CondCont', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild'),
(N'DEM', N'PARKER', N'Drug', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild'),
(N'DEM', N'PARKER', N'DupClass', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'PARKER', N'DupGen', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'PARKER', N'DupIng', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', NULL),
(N'DEM', N'PARKER', N'FoodInt', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate'),
(N'DEM', N'PCC', N'CondCont', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', NULL, NULL, N'Severe,Intermediate'),
(N'DEM', N'PCC', N'Drug', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', NULL, NULL, N'Severe,Intermediate'),
(N'DEM', N'PCC', N'DupClass', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', NULL, NULL, NULL),
(N'DEM', N'PCC', N'DupGen', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', NULL, NULL, NULL),
(N'DEM', N'PCC', N'DupIng', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', NULL, NULL, NULL),
(N'DEM', N'PCC', N'FoodInt', CAST(0x0000A15000E8E0CC AS DateTime), N'N', NULL, NULL, NULL),
(N'DEM', N'PCM', N'CondCont', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild'),
(N'DEM', N'PCM', N'Drug', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild'),
(N'DEM', N'PCM', N'DupClass', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'Y', N'Y', NULL),
(N'DEM', N'PCM', N'DupGen', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'PCM', N'DupIng', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'PCM', N'FoodInt', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe'),
(N'DEM', N'TC.UK', N'CondCont', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild'),
(N'DEM', N'TC.UK', N'Drug', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe,Intermediate,Mild'),
(N'DEM', N'TC.UK', N'DupClass', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'Y', N'Y', NULL),
(N'DEM', N'TC.UK', N'DupGen', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'TC.UK', N'DupIng', CAST(0x0000A15000E8E0CC AS DateTime), N'N', N'N', N'N', NULL),
(N'DEM', N'TC.UK', N'FoodInt', CAST(0x0000A15000E8E0CC AS DateTime), N'Y', N'Y', N'Y', N'Severe')
-- Solution
SELECT
[SourceID],
[MisIcgID],
[InteractionID],
[RowUpdateDateTime],
[InteractionCheckSeverity],
x.[Column],
x.Value
FROM MisIcg_InteractionChecks i
CROSS APPLY (
SELECT *
FROM (
SELECT [Column] = 'InteractionCheckMedication', Value = InteractionCheckMedication UNION ALL
SELECT 'InteractionCheckComponent', InteractionCheckComponent UNION ALL
SELECT 'InteractionCheckIntravenous', InteractionCheckIntravenous
) d
WHERE ([Column] = 'InteractionCheckMedication' AND Value = 'Y')
OR ([Column] = 'InteractionCheckComponent' AND Value = 'Y')
OR ([Column] = 'InteractionCheckIntravenous' AND Value = 'N')
) x
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply