Complicated concatenate function

[benking9987]

Hello and thanks in advance for any help rendered.

I am looking to concatenate some values that span multiple columns as well as rows. I'm looking to make this:

SHIPMENT# BOX# ITEM# EXPIRATION QTY
SHIPMENT1 BOX1 ITEM1 5/20/2020 4
SHIPMENT1 BOX1 ITEM2 5/30/2020 50
SHIPMENT1 BOX1 ITEM3 NULL 10

Into this:

"CONSTANT,PO:SHIPMENT1,ITEM:ITEM1,QTY:4,EXP:202005,ITEM:ITEM2,QTY:50,EXP:203005,ITEM:ITEM3,QTY:10"

Notice that in the string I want to create I did not include a space holder for the null value in the expiration date in the third line.

Wondering the best way to go about this.

Thanks again in advance for any help!

[yosiasz]

why ?

IF OBJECT_ID('tempdb..#showlove') IS NOT NULL
    DROP TABLE #showlove
create table #showlove(SHIPMENT varchar(50), BOX varchar(50), ITEM varchar(50), EXPIRATION date, QTY int)

insert into #showlove
select 'SHIPMENT1', 'BOX1', 'ITEM1', '5/20/2020', 4 union
select 'SHIPMENT1', 'BOX1', 'ITEM2', '5/30/2020', 50 union
select 'SHIPMENT1', 'BOX1', 'ITEM3', NULL, 10

select 'CONSTANT,PO:' +  SHIPMENT + ',' + a
from (
	select distinct SHIPMENT,
					(SELECT 'ITEM:' + ITEM + 
					',QTY:' + CAST(QTY as varchar(100)) + ',' + 
					case 
						when EXPIRATION  is null then '' 
						else 'EXP:' + CONVERT(varchar(6), EXPIRATION,12) + ',' end AS [text()]
					FROM #showlove ST1
					WHERE ST1.SHIPMENT = ST2.SHIPMENT
					FOR XML PATH ('')
					) as a
	  FROM #showlove ST2
) a

[harishgg1]

hi

i hope this helps ..

drop create data ..

drop table #abc 
go 

create table #abc 
(
SHIPMENT# varchar(100) null,
BOX# varchar(100) null,
ITEM# varchar(100) null,
EXPIRATION datetime null,
QTY int null 
)
go 

insert into #abc select 'SHIPMENT1','BOX1','ITEM1','5/20/2020', 4
insert into #abc select 'SHIPMENT1','BOX1','ITEM2','5/30/2020', 50
insert into #abc select 'SHIPMENT1','BOX','ITEM3',NULL ,10
go

SQL..

; WITH cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY [shipment#]) AS rn, 
                * 
         FROM   #abc) 
SELECT * 
FROM   cte a 
       JOIN cte b 
         ON a.rn + 1 = b.rn 
       JOIN cte c 
         ON a.rn + 2 = c.rn 

go