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: 245813 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?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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: 245813 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;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.