CODEWITHSUNDEEP
sql-servert-sql
bapster
bapster

Reputation: 161

Displaying data in a different way

I have a log table that looks like this:

ProductId | OldDescription | NewDescription | OldTagId | NewTagId |
-------------------------------------------------------------------
12345     |  description1  |  description2  |     1    |      5   |

and I want to display it this way:

ProductId | ChangeId     |      OldVal    |    NewVal    | 
----------------------------------------------------------
12345     |      1       |  description1  | description2 |
12345     |      2       |       1        |      5       |

Where the data in the ChangeId corresponds to the type of the value changed (Description, TagId)

How could I approach this?

Thank you

Upvotes: 0

Views: 58

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

Just another option via CROSS APPLY

Example

Declare @YourTable Table ([ProductId] varchar(50),[OldDescription] varchar(50),[NewDescription] varchar(50),[OldTagId] int,[NewTagId] int)
Insert Into @YourTable Values 
 (12345,'description1','description2',1,5)

Select ProductID 
      ,B.*
 From  @YourTable A
 Cross Apply ( values (1,[OldDescription],[NewDescription])
                     ,(2,left([OldTagId],25),left([NewTagId],25))
             ) B(ChangeID,OldVal,NewVal)

Returns

ProductID   ChangeID    OldVal          NewVal
12345       1           description1    description2
12345       2           1               5

Just for fun:

I saw the comment of 30 columns. If performance is NOT essential, here is option that will dynamically pivot your data without actually using dynamic SQL

Select *
 From  (
        Select ProductID 
              ,C.*
         From  @YourTable A
         Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
         Cross Apply (
                        Select Item      = left(xAttr.value('local-name(.)', 'varchar(100)'),3)+'Val'
                              ,Value     = xAttr.value('.','varchar(100)')
                              ,ChangeID  = ((row_number() over (order by (select null)) - 1 ) / 2)+1
                         From  XMLData.nodes('//@*') xNode(xAttr)
                         Where xAttr.value('local-name(.)','varchar(100)') not in ('ProductID','Other','ColumnsToExclude')
                     ) C
       ) src
 Pivot ( max(Value) for Item in ([OldVal],[NewVal]) ) pvt

Upvotes: 4

Diego Barbosa
Diego Barbosa

Reputation: 31

See if something like that works for you:

SELECT ProductId,
       1              AS ChangeId,
       OldDescription AS OldVal,
       NewDescription AS NewVal
FROM   log
UNION
SELECT ProductId,
       2        AS ChangeId,
       OldTagId AS OldVal,
       NewTagId AS NewVal
FROM   log
ORDER  BY ProductId,
          ChangeId

Upvotes: 0

Related Questions