SQL Help
In my SQL table I have columns Prg1 - Prg6. All columns might not contain data all the time.
I need to check whether there is a value for each column and if value exists, display that if not display EMPTY as a new column called NewColumn.
If a record has values in more than one column, I need the value from the very first column displayed.
CREATE TABLE #t1([VID] [varchar](100) NULL, [OrdDate] [datetime] NOT NULL,
[Prg1] [varchar](10) NULL, [Prg2] [varchar](10) NULL, [Prg3] [varchar](10), [Prg4] [varchar](10), [Prg5] [varchar](10), [Prg6] [varchar](10)
)
ON [PRIMARY]
insert into #t1 values('11111','2022-01-10 13:37:06.000','P1','P2','P3','','','')
insert into #t1 values('11111','2022-01-10 13:37:06.000','P1','','','','','')
insert into #t1 values('11111','2022-01-10 13:37:06.000','','P2','','','','')
insert into #t1 values('11111','2022-01-10 13:37:06.000','P1','P2','P3','P4','P5','P6')
insert into #t1 values('11111','2022-01-10 13:37:06.000','','','P3','','','')
insert into #t1 values('11111','2022-01-10 13:37:06.000','','P2','P3','P4','','')
insert into #t1 values('11111','2022-01-10 13:37:06.000','','','P3','P4','P5','')
insert into #t1 values('11111','2022-01-10 13:37:06.000','','','','','','')
insert into #t1 values('11111','2022-01-10 13:37:06.000','','','','','','P5')
select * from #t1
drop table #t1
With the above data, I am looking for the following result.
NewColumn VID OrdDate Prg1 Prg2 Prg3 Prg4 Prg5 Prg6
P1 11111 2022-01-10 13:37:06.000 P1 P2 P3
P1 11111 2022-01-10 13:37:06.000 P1
P2 11111 2022-01-10 13:37:06.000 P2
P1 11111 2022-01-10 13:37:06.000 P1 P2 P3 P4 P5 P6
P3 11111 2022-01-10 13:37:06.000 P3
P2 11111 2022-01-10 13:37:06.000 P2 P3 P4
P3 11111 2022-01-10 13:37:06.000 P3 P4 P5
EMPTY 11111 2022-01-10 13:37:06.000
P5 11111 2022-01-10 13:37:06.000 P5
Any help is greatly appreciated.
It seems you're looking for the COALESCE function. If the empty values are stored as '' then NULLIF returns NULL. COALESCE returns the first (left to right of the parameter list) non NULL value. ISNULL returns 'EMPTY' if there are no values
select isnull(coalesce(nullif([Prg1], ''),
nullif([Prg2], ''),
nullif([Prg3], ''),
nullif([Prg4], ''),
nullif([Prg5], ''),
nullif([Prg6], '')), 'EMPTY') NewCol, *
from #t1 t;
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
July 25, 2022 at 2:58 pm
Thanks for the response. This works well. Much appreciated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply