-1

enter image description here

Please help me to solve my below query - I have the following data in my table and below is my query

select QE.ID as ID , QE.DATED AS DATE
from
SYSTEM_ENTRY QE
LEFT JOIN SYSTEM_SUB SSS ON (QE.LINK_ID1 = SSS.SUBMISSION_ID AND QE.ID = SSS.ID)
where QE.ID =  62733 AND LTRIM(RTRIM(QE.TASK_NAME)) in
(
'outcome of Require Changes'
)

I want the data to look like this

ID        date1                    date2                    date3                    date4
62733     2020-04-07 10:49:05.187  2020-04-10 15:24:10.037  2020-04-14 11:33:47.357  2020-04-23 12:18:08.753

I just don't know how many rows will be returned and it will have dynamic cols. Please help me, is it possible?

I have tried the below query but it's not returning expected results.

select * from
(select
(QE.ID ) as ID, CONVERT(nvarchar, QE.DATED, 120) as d1
from
SYSTEM_ENTRY QE
LEFT JOIN SYSTEM_SUB SSS ON (QE.LINK_ID1 = SSS.SUBMISSION_ID AND QE.ID = SSS.ID)
where QE. D =  62733 AND LTRIM(RTRIM(QE.TASK_NAME)) in
(
'outcome of Require Changes'
))T PIVOT ( sum(id) FOR d1 IN ( [date1], [date2], [date3], [date4] ) ) AS pivot_table;

enter image description here

shanky
  • 751
  • 1
  • 16
  • 46
  • if you don't know how many rows will be returned, you can reach the maximum number of columns supported by SQL which is 1024. Given that you'll have less than 1023 dates, then you'll need to look a dynamic pivot script like described https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query – Luis LL Jul 21 '20 at 18:31

1 Answers1

0

As far as I understood, you just need the pivot table. If that's the case, you should use PIVOT operator.

Here's the link to learn more about it:

enter link description here

Bakhodir
  • 33
  • 7
  • Thanks for your link. I've updated my question with link description however it's not showing me the right results. – shanky Jul 21 '20 at 19:34
  • @shanky to be honest, can't do anything without seeing the DB itself, but here is the similar code I used to something like that https://www.db-fiddle.com/f/iYRhUAvjtBcRnW2Fmx4VwS/0 – Bakhodir Jul 22 '20 at 18:29