transpose columns to row using cross apply

Question

Ori Data

+--------+-------+---------------+------------+--------+--------------+-------+
| RowNum | SeqNo |     Name      | NameReason | Gender | GenderReason |  ID   |
+--------+-------+---------------+------------+--------+--------------+-------+
|      1 | A123  | IronMan       |            | P      |              | E8888 |
|      2 | A123  | CaptainMarvel | A          | L      | A            | E8888 |
|      3 | A123  | Yoooo         |            |        |              | E8888 |
|      4 | A123  | Heyyy         |            |        |              | E8888 |
|      1 | B456  | Hey           |            |        |              | D2222 |
|      2 | B456  | DOTS          | A          |        |              | D2222 |
|      1 | C1234 | Hulk          |            |        |              | E8989 |
|      2 | C1234 | Cap           |            |        |              | E8989 |
|      3 | C1234 | Hat           |            |        |              | E8989 |
+--------+-------+---------------+------------+--------+--------------+-------+

Result I Want

+-------+-------+---------+---------------+----------+--------+
| SeqNo |  ID   | ColName |  From_Value   | To_Value | Reason |
+-------+-------+---------+---------------+----------+--------+
| A123  | E8888 | Name    | CaptainMarvel | IronMan  | A      |
| A123  | E8888 | Gender  | L             | P        | A      |
| B456  | D2222 | Name    | DOTS          | Hey      | A      |
| C1234 | E8989 | Name    | Cap           | Hulk     |        |
+-------+-------+---------+---------------+----------+--------+

Query:

select a.rownum, a.seqno, a.name, a.id,
       b.*
from #A a cross apply
( values ('Name', NameReason)
         ('Gender', GenderReason)
    ) b (colname, Reason)
where reason is not null

Note: Want to find the changes. RowNum=1 is the latest update (To_Value), RowNum=2 (From_Value). Rownum already filter the top updated result (rownumber () over (partitition) . I just need rownum=2(from_Value), rownum=1(to_value) others ignore, because 1 application can update 100times, i just need find the latest changes will do.

From the query above, how can I modify to the result I want? How can I add from_value and to_value, reason?

Please explain the logic for the transformation. It is not obvious. — Gordon Linoff, Mar 11 '19 at 11:38
@GordonLinoff hi. I just want to find what are the changes. RowNum=1 definitely is the latest update. Result I want, From_Value=RowNum=2, To_Value=RowNum=1. I just want to know how to modify my script so can cater From_Value and To_Value — user3542587, Mar 11 '19 at 11:42

LukStorms · Accepted Answer · 2019-08-07T13:59:06.327

1

Assuming you can link the "From" record on the RowNum + 1

SELECT q.SeqNo, q.ToName AS Name, q.ID, ca.ColName, ca.From_Value, ca.To_Value 
FROM
(
  SELECT 
   a1.ID, a1.SeqNo, a1.NameReason, a1.GenderReason, a1.RowNum, 
   a2.Name as FromName, a1.Name as ToName,
   a2.Gender AS FromGender, a1.Gender AS ToGender
  FROM #A a1
  JOIN #A a2 ON (a2.ID = a1.ID AND a2.RowNum = a1.RowNum + 1)
  WHERE (a1.NameReason IS NOT NULL OR a1.GenderReason IS NOT NULL)
    AND a1.Name != a2.Name
) q
CROSS APPLY (VALUES 
 (1, 'Name', NameReason, FromName, ToName), 
 (2, 'Gender', GenderReason, FromGender, ToGender)
) ca (ColSeq, ColName, Reason, From_Value, To_Value)
WHERE ca.Reason IS NOT NULL
ORDER BY q.SeqNo, ca.ColSeq;

But based on the comments only RowNum 1 & 2 matter.
Then it can be simplified.

SELECT q.SeqNo, q.ToName AS Name, q.ID, ca.ColName, ca.From_Value, ca.To_Value 
FROM
(
  SELECT 
   a1.ID, a1.SeqNo, 
   a1.NameReason, a2.Name as FromName, a1.Name as ToName,
   a1.GenderReason, a2.Gender AS FromGender, a1.Gender AS ToGender
  FROM #A a1
  JOIN #A a2 ON (a2.ID = a1.ID AND a2.RowNum = 2)
  WHERE a1.RowNum = 1
) q
CROSS APPLY (VALUES 
 (1, 'Name', NameReason, FromName, ToName), 
 (2, 'Gender', GenderReason, FromGender, ToGender)
) ca (ColSeq, ColName, Reason, From_Value, To_Value)
WHERE ca.Reason IS NOT NULL
ORDER BY q.SeqNo, ca.ColSeq;

Result:

SeqNo Name    ID    ColName From_Value    To_Value
----- ------- ----- ------- ------------- --------
A123  IronMan E8888 Name    CaptainMarvel IronMan
A123  IronMan E8888 Gender  L             P
B456  Hey     D2222 Name    DOTS          Hey

A test on db<>fiddle here

edited Aug 07 '19 at 13:59

answered Mar 11 '19 at 12:52

LukStorms

28,916
5
31
45

what if i just want seqno, id, colname, from_value,to_value,reason? with this query, it show all changes. I just want rownum=1, and rownum=2. 2(from_value) > 1(to_value) others ignore it. because 1 application i might have update 100 times (impossible show all, so just need top 2) will do. these rownum did overpartition (sort by date and idno) – user3542587 Mar 11 '19 at 14:17
Oh... well, that's easy to do in this SQL. Just change the WHERE clause in the sub-query. `WHERE a1.RowNum = 1` – LukStorms Mar 11 '19 at 14:21
seems like working. i need time to 'absorb aka understand' the query. – user3542587 Mar 11 '19 at 14:25
If that RowNum was calculated via a `ROW_NUMBER(PARTITION BY ID ORDER BY [date] desc)` then the JOIN on a2 doesn't need the `AND a2.SeqNo = a1.SeqNo` – LukStorms Mar 11 '19 at 14:28
@luke your answer is great. However if i have reason for name and update 2 times, i just need the most recent changes, how can i amend it. Btw, im using your first script instead of 1. – user3542587 Aug 07 '19 at 02:06
@user3542587 Sorry, but not exactly sure what you mean. But when you mention something like "most recent changes", then what's often used is the [ROW_NUMBER](https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql) window function. Basically, it allows to generate a number based on an ORDER, then to get the first you select where it's 1. – LukStorms Aug 07 '19 at 07:35
Maybe I misunderstand, but if the data contains the same name in sequence, then you could maybe add something like this to the WHERE clause of the inner query : `AND a1.Name != a2.Name` – LukStorms Aug 07 '19 at 07:54
That extra record `(1,'B456','Hey',null,null,null,'D2222')` won't matter for the first query. Since both the reasons are null. – LukStorms Aug 07 '19 at 13:57

PSK · Answer 2 · 2019-03-11T12:22:28.413

You can write your query like following using UNION.

  select t1.SeqNo, 
       t1.Name, 
       t1.ID, 
       'Name'                  as ColName, 
       (select name 
        from   mytable mt 
        where  mt.SeqNo = t1.seqNo 
               and RowNum = 2) as From_Value, 
       t1.Name                 as To_Value 
from   mytable t1 
where  t1.RowNum = 1 
union all
select * 
from   (select t1.SeqNo, 
               t1.Name, 
               t1.ID, 
               'Gender'                as ColName, 
               t1.Gender               as From_Value, 
               (select Gender 
                from   mytable mt 
                where  mt.SeqNo = t1.seqNo 
                       and RowNum = 1) as To_Value 
        from   mytable t1 
        where  t1.RowNum = 2) t 
where  From_Value is not null

Online Demo

Output

+-------+---------------+-------+---------+---------------+----------+
| SeqNo | Name          | ID    | ColName | From_Value    | To_Value |
+-------+---------------+-------+---------+---------------+----------+
| A123  | CaptainMarvel | E8888 | Gender  | L             | P       |
+-------+---------------+-------+---------+---------------+----------+
| A123  | IronMan       | E8888 | Name    | CaptainMarvel | IronMan  |
+-------+---------------+-------+---------+---------------+----------+
| B456  | Hey           | D2222 | Name    | DOTS          | Hey      |
+-------+---------------+-------+---------+---------------+----------+

Im dealing with 10mils records + 20+ cols. When i run your script, i got error " subquery return > 1 value" — user3542587, Mar 11 '19 at 12:48

transpose columns to row using cross apply

2 Answers2