2nd+ rows as columns

1

Hi

given the following data

create table #rma(rmaid int identity (1,1) primary key, SN varchar(50), rmaissue varchar(50), issuedate date)

insert into #rma 
select 'Tezzla', 'Fire!', '2017-06-28' union
select 'Totyota', 'Air Hag', '2017-06-28' union
select 'Totyota', 'Sudden Acceleratiog', '2017-06-29' union
select 'Totyota', 'Sudden Acceleratiog', '2017-06-30' union
select 'Ouber', 'hoo eez she?', '2015-06-28' union
select 'Ouber', 'I am stepping down', '2015-06-29' union
select 'Ouber', 'safe place to work...sure', '2015-06-27'

select * 
from #rma

drop table #rma

How can query the data so that each 2nd occurrence for the same SN becomes an extra column..dynamically :frowning:

Thanks!

2

Not sure what you're looking for here...???

This maybe...

WITH 
	cte_rma AS (
		SELECT 
			r.rmaid, 
			r.SN, 
			r.rmaissue, 
			r.issuedate,
			rn = ROW_NUMBER() OVER (PARTITION BY r.SN ORDER BY r.rmaid)
		FROM
			#rma r
		)
SELECT 
	rmaid = MIN(CASE WHEN rma.rn = 1 THEN rma.rmaid END), 
	rma.SN, 
	rmaissue = MIN(CASE WHEN rma.rn = 1 THEN rma.rmaissue END), 
	issuedate = MIN(CASE WHEN rma.rn = 1 THEN rma.issuedate END),
	extracolumn = MIN(CASE WHEN rma.rn = 2 THEN rma.rmaissue END)
FROM
	cte_rma rma
GROUP BY 
	rma.SN;
1 Like
3

Jason!

That worked, looks great, thanks much!

1 Like
4

Sorry I take that back Jason :frowning: let me post the result I want to see.

5

Edit: Spoke too soon...

6

This was what I was looking for. I may need to do some sort of dynamic query.

SN	        rma1	    rma2	                        rma3
Ouber	hoo eez she?	I am stepping down	            safe place to work...sure
7

Hold a sec... I can demonstrate the dynamic version... BRB.

8

This should do the trick...

DECLARE 
	@sql NVARCHAR(4000) = N'',
	@DeBug BIT = 0;

WITH 
	cte_DistinctRN AS (
		SELECT DISTINCT 
			rn = ROW_NUMBER() OVER (PARTITION BY rma.SN ORDER BY rma.rmaid)
		FROM 
			#rma rma
		)
SELECT 
	@sql = CONCAT(@sql, N',', CHAR(13), CHAR(10), N'	rma_', drn.rn, N' = MIN(CASE WHEN rma.rn = ', drn.rn, N' THEN rma.rmaissue END)')
FROM 
	cte_DistinctRN drn;

SET @sql = CONCAT(N'
SELECT 
	rma.SN, ',
	STUFF(@sql, 1, 2, ''), N'
FROM
	(SELECT *, rn = ROW_NUMBER() OVER (PARTITION BY rma.SN ORDER BY rma.rmaid)
	FROM #rma rma) rma
GROUP BY 
	rma.SN;')

IF @DeBug = 1
BEGIN 
	PRINT (@sql);
END;
ELSE 
BEGIN 
	EXEC sys.sp_executesql @sql;
END;

It should go without saying, if you can fix the output column to a finite number, it will be far more efficient... If not, the above should work.

1 Like
9

yee haw!! That did it Jason!

Again thanks very much!

10

LOL... Glad to help :thumbsup: