Find a sequence number with more than one day and return the data in the same row

Other SQL Server Topics
1

Hi

I need to do a query that when finds a sequence number with more than one occurence and day (day0 to day d62) returns the data in the same row. For example the sequence number 939860789 occurs in 8 days but each data are in a different row and I need all the data of this sequence number stays in the same row of d0/5770 that is the first occurrence.

IF OBJECT_ID('test01..#TestSample') IS NOT NULL DROP TABLE #TestSample;

CREATE TABLE #TestSample (

seq_number CHAR(9),

d0 VARCHAR(6), d1 VARCHAR(6), d2 VARCHAR(6), d3 VARCHAR(6), d4 VARCHAR(6), d5 VARCHAR(6), d6 VARCHAR(6), d7 VARCHAR(6), d8 VARCHAR(6), d9 VARCHAR(6), d10 VARCHAR(6),

d11 VARCHAR(6), d12 VARCHAR(6), d13 VARCHAR(6), d14 VARCHAR(6), d15 VARCHAR(6), d16 VARCHAR(6), d17 VARCHAR(6), d18 VARCHAR(6), d19 VARCHAR(6), d20 VARCHAR(6))

INSERT INTO #TestSample (seq_number, d0) VALUES

('939860783', '632'),

('939860785', '6851'),

('939860789', '5770'),

('939860789', 'V4579'),

('939860789', '4019'),

('939860789', '2867'),

('939860789', '25000'),

('939860789', '2722'),

('939860789', '42731'),

('939860789', 'V5861')

SELECT

seq_number,

d0 = MAX(CASE WHEN rn = 0 THEN d0 ELSE NULL END),

d1 = MAX(CASE WHEN rn = 1 THEN d0 ELSE NULL END),

d2 = MAX(CASE WHEN rn = 2 THEN d0 ELSE NULL END),

d3 = MAX(CASE WHEN rn = 3 THEN d0 ELSE NULL END)

FROM (

SELECT seq_number, d0, rn = ROW_NUMBER() OVER(PARTITION BY seq_number ORDER BY (SELECT NULL))-1

FROM #TestSample

) d

GROUP BY d.seq_number

Thanks

2

I don't know if this will help but:

IF OBJECT_ID('temdb.dbo.#TestSample') IS NOT NULL DROP TABLE #TestSample;
CREATE TABLE #TestSample (
seq_number CHAR(9), 
d0 VARCHAR(6), d1 VARCHAR(6), d2 VARCHAR(6), d3 VARCHAR(6), d4 VARCHAR(6), d5 VARCHAR(6), d6 VARCHAR(6), d7 VARCHAR(6), d8 VARCHAR(6), d9 VARCHAR(6), d10 VARCHAR(6), 
d11 VARCHAR(6), d12 VARCHAR(6), d13 VARCHAR(6), d14 VARCHAR(6), d15 VARCHAR(6), d16 VARCHAR(6), d17 VARCHAR(6), d18 VARCHAR(6), d19 VARCHAR(6), d20 VARCHAR(6))
INSERT INTO #TestSample (seq_number, d0, d1, d2, d3, d4, d5, d6, d7) VALUES 
('939860783', '632', NULL, NULL, NULL, NULL, NULL, NULL, NULL), 
('939860785', '6851', NULL, NULL, NULL, NULL, NULL, NULL, NULL), 
('939860789', '5770', NULL, NULL, NULL, NULL, NULL, NULL, NULL), 
('939860789', NULL, 'V4579', NULL, NULL, NULL, NULL, NULL, NULL), 
('939860789', NULL, NULL, '4019', NULL, NULL, NULL, NULL, NULL), 
('939860789', NULL, NULL, NULL, '2867', NULL, NULL, NULL, NULL), 
('939860789', NULL, NULL, NULL, NULL, '25000',NULL, NULL,  NULL), 
('939860789', NULL, NULL, NULL, NULL, NULL, '2722', NULL, NULL), 
('939860789', NULL, NULL, NULL, NULL, NULL, NULL, '42731', NULL), 
('939860789', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'V5861')
SELECT ts.seq_number
     , ts.d0
     , ts.d1
     , ts.d2
     , ts.d3
     , ts.d4
     , ts.d5
     , ts.d6
     , ts.d7
FROM #TestSample ts;
DROP TABLE #TestSample
GO

CREATE TABLE #TestSample
   (
     seq int
   , val varchar(6)
   , d int
   );
INSERT #TestSample
        (seq, val, d)
    VALUES
('939860783', '632', 0), 
('939860785', '6851', 0), 
('939860789', '5770', 0), 
('939860789', 'V4579', 1), 
('939860789', '4019', 2), 
('939860789', '2867', 3), 
('939860789', '25000', 4), 
('939860789', '2722', 5), 
('939860789', '42731', 6), 
('939860789', 'V5861', 7);
SELECT seq, [0] d0, [1] d1, [2] d2, [3] d3, [4] d4, [5] d5, [6] d6, [7] d7
FROM #TestSample ts
PIVOT (
         Max(val)
      FOR d IN ( [0] , [1] , [2] , [3] , [4] , [5] , [6] , [7])
      ) pvt

GO