Need to write query

Question

Post reply

Need to write query

patla4u

SSCrazy

Points: 2103
More actions
May 29, 2019 at 10:22 pm

#3648435

Hi,
Can you please help me write a code.

I am having this in sql server

I would this output

Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247037 More actions · Answer 1

Can you briefly explain why this is required?

And if there are three rows of source data, what do you want to see?

patla4u SSCrazy Points: 2103 More actions · Answer 2

It should be always 1 or 2 in Source data. Want to display different column in report based on event.

Phil Parkin SSC Guru Points: 247037 More actions · Answer 3

Try this

DROP TABLE IF EXISTS #SomeTable;

CREATE TABLE #SomeTable
(
    Id INT
   ,EventId INT
   ,A VARCHAR(20)
   ,B VARCHAR(20)
   ,C VARCHAR(20)
   ,D VARCHAR(20)
);

INSERT #SomeTable
(
    Id
   ,EventId
   ,A
   ,B
   ,C
   ,D
)
VALUES
(1, 37, NULL, NULL, '1L', NULL)
,(1, 292, 'Positive', 'Positive', NULL, NULL);

WITH Results
AS (SELECT st.Id
          ,st.EventId
          ,st.A
          ,st.B
          ,st.C
          ,st.D
          ,EventId2 = LEAD(st.EventId, 1, NULL) OVER (PARTITION BY st.Id ORDER BY st.EventId)
          ,A2 = LEAD(st.A, 1, NULL) OVER (PARTITION BY st.Id ORDER BY st.EventId)
          ,B2 = LEAD(st.B, 1, NULL) OVER (PARTITION BY st.Id ORDER BY st.EventId)
          ,C2 = LEAD(st.C, 1, NULL) OVER (PARTITION BY st.Id ORDER BY st.EventId)
          ,D2 = LEAD(st.D, 1, NULL) OVER (PARTITION BY st.Id ORDER BY st.EventId)
          ,rn = ROW_NUMBER() OVER (PARTITION BY st.Id ORDER BY st.EventId)
    FROM #SomeTable st)
SELECT Results.Id
      ,Results.EventId
      ,Results.A
      ,Results.B
      ,Results.C
      ,Results.D
      ,Results.EventId2
      ,Results.A2
      ,Results.B2
      ,Results.C2
      ,Results.D2
FROM Results
WHERE Results.rn = 1;