I am including some sample data to explain the requirement. I need a column that gives the total of Prg1 to Prg6 if the values are different
Ex:
Need to compare each field data from prg1 to prg6.
If all 6 fields have different values (p1, p2, p3, p4, p5, p6), I need the count as 6
If only 3 got different values (p1, p1, p1, p2, p3), I need it as 3
('',p1,p1,'',p2,'') count should be 2
('','','','',p2,'') count should be 1
I hope I explained it properly. Any help is greatly appreciated.
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
July 26, 2022 at 9:00 pm
I wouldn't choose to create a table with that structure, but this should work:
SELECT VID, OrdDate, COUNT(DISTINCT(v.val))
FROM #T1
CROSS APPLY (VALUES (Prg1),(Prg2),(Prg3),(Prg4),(Prg5),(Prg6)) v(val)
GROUP BY VID, OrdDate;
July 26, 2022 at 9:21 pm
Thanks for the response. Unfortunately, I have no control over the data. It's a 3rd party database. This is how they designed it.
Your solution groups by vid and orddate.
What I want is all the data as is with a field at the end with the count. Is this possible without grouping?
Looking for the below result.
FieldCount VID OrdDate Prg1 Prg2 Prg3 Prg4 Prg5 Prg6
3 11111 2022-01-10 13:37:06.000 P1 P2 P3
1 11111 2022-01-10 13:37:06.000 P1
1 11111 2022-01-10 13:37:06.000 P2
6 11111 2022-01-10 13:37:06.000 P1 P2 P3 P4 P5 P6
1 11111 2022-01-10 13:37:06.000 P3
3 11111 2022-01-10 13:37:06.000 P2 P3 P4
3 11111 2022-01-10 13:37:06.000 P3 P4 P5
0 11111 2022-01-10 13:37:06.000
1 11111 2022-01-10 13:37:06.000 P5
2 11111 2022-01-10 13:37:06.000 P2 P2 P4
July 26, 2022 at 10:57 pm
like this?
SELECT
FieldCount = (CASE WHEN Prg1 ='' THEN 0 ELSE 1 END +
CASE WHEN Prg2 ='' THEN 0 ELSE 1 END +
CASE WHEN Prg3 ='' THEN 0 ELSE 1 END +
CASE WHEN Prg4 ='' THEN 0 ELSE 1 END +
CASE WHEN Prg5 ='' THEN 0 ELSE 1 END +
CASE WHEN Prg6 ='' THEN 0 ELSE 1 END ),
VID,
OrdDate,
prg1,
prg2,
prg3,
prg4,
prg5,
prg6
FROM #t1;
July 27, 2022 at 1:34 am
Thanks. Almost what I need. Is it possible to compare the values? If the same value exists, I don't want it to count.
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','','','P5')
Last row should give 1 as P5 is in 2 fields.
July 27, 2022 at 2:13 am
Transpose table, do a distinct count, transpose back.
Perhaps a small tweak to Piet's 1st answer
SELECT src.*, v.val
FROM #t1 AS src
CROSS APPLY (SELECT val = COUNT(DISTINCT(NULLIF(RTRIM(x.val), '')))
FROM (VALUES (src.Prg1),(src.Prg2),(src.Prg3),(src.Prg4),(src.Prg5),(src.Prg6)
) AS x(val)
) AS v(val);
July 27, 2022 at 3:38 pm
Oh, NULLIF!! I knew I was missing something with the NULLs. <g>
July 28, 2022 at 6:38 pm
Thanks a lot for the replies. It works.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy