How to roll up multiple row into one

Transact-SQL
1

Hello,

I need to combine one or more rows into a single row based on key fields.

Here's an excerpt of the raw data:

image
image.png1221×385 13.7 KB

And this is what have so far. I'm using the OPCItem field to match rows.

image
image.png929×195 7.34 KB

This is the code that produces the above:

select a1.facility, a1.opcitem, a1.ico9desc, a2.ico10desc
from awxcomparison a1, awxcomparison a2
where a1.opcitem=a2.opcitem
and (a1.ico9desc is not null
and a2.ico10desc is not null)
and a1.facility='DENVER UNIT BATTERY 5'
order by opcitem

But in the first screenshot, notice that there is a lone record at ReadDints[19].13. My code omits this record, but I need to include any record that has nulls in either of the two Desc columns.

Can you help? Thanks in advance for your help.

Doug

2

here's what I think you are looking for, but without DDL and sample data, I can only guess. Also, the syntax you are using is deprecated and you should use current ANSI standards

drop table if exists #temp


Create table #Temp (ID int, 
					Facility nvarchar(50),
					OPCItem nvarchar(100),
					ICO9Desc nvarchar(100),
					ICO10Desc nvarchar(100))

insert into #Temp(Facility, OPCItem, ICO9Desc, ICO10Desc)
values 
('DENVER UNIT BATTERY 5','ReadDints[19].6','DU Batt 5 VRU 2 PLC Comm Trouble  (DUB5)',null),
('DENVER UNIT BATTERY 5','ReadDints[19].6',null, 'DU Batt 5 VRU 2 PLC Comm Trouble - Alarm'),
('DENVER UNIT BATTERY 5','ReadDints[19].7','DU Batt 5 VRU Starter 520 Comm Trouble  (DUB5)',null),
('DENVER UNIT BATTERY 5','ReadDints[19].7',null, 'Denver Unit Bat 5 - 520 Control Building Enclosure - Alarm'),
('DENVER UNIT BATTERY 5','ReadDints[19].8','DU Batt 5 VRU Starter 530 Comm Trouble  (DUB5)',null),
('DENVER UNIT BATTERY 5','ReadDints[19].8',null, 'Denver Unit Bat 5 - 530 Control Building Enclosure - Alarm'),
('DENVER UNIT BATTERY 5','ReadDints[19].9','DU Batt 5 VRU Starter 460 Comm Trouble  (DUB5)',null),
('DENVER UNIT BATTERY 5','ReadDints[19].9',null, 'Denver Unit Bat 5 - 460 Control Building Enclosure - Alarm'),
('DENVER UNIT BATTERY 5','ReadDints[19].13',null, null)


select a1.facility, a1.opcitem, max(a1.ico9desc) ico9desc, max(ico10desc) ico10desc
from #Temp a1
where a1.facility='DENVER UNIT BATTERY 5'
group by a1.facility, a1.opcitem
order by opcitem
3

Mike, thanks so much. It works perfectly! I would have never thought to use max with text data.

4

Mike, thanks so much. It works perfectly! I would have never thought to use max with text.