Converting columns into rows against each row

Question

I apologize if the question is a duplicate but I have some requirement which is getting on my nerves. And after many attempts I am unable to solve it. The requirement is I have a table which has data as follows:

CHANGE_NUMBER   ITEM_NUMBER REV BOM_ITEM    FIND_NUMBER QUANTITY
MD04086         710851-1    B   230248-0           0    9
MD04086         710851-1    B   230289-0           0    4
MD04086         710851-1    B   230292-0           0    1
MD04086         710851-1    B   230298-0           0    2

What I want to do with this data is to make it in the following format:

Column_name     Column_Value

CHANGE_NUMBER   MD04086
ITEM_NUMBER     710851-1
REV             B
BOM_ITEM        230248-0
FIND_NUMBER     0
QUANTITY        9
CHANGE_NUMBER   MD04086
ITEM_NUMBER     710851-1
REV             B
BOM_ITEM        230289-0
FIND_NUMBER     0
QUANTITY        4

So far I have used the following query but it does not provide me what I am trying to achieve

with q1 as
(select  'itemRev' as column_name,[CHANGE_NUMBER] as column_value  from  generalassemblies_data_final
union all

select  'itemNumber' as column_name,[ITEM_NUMBER] as column_value  from  generalassemblies_data_final
union all

select  'REV' as column_name,[REV] as column_value  from  generalassemblies_data_final
union all

select  'BOM_ITEM' as column_name,[BOM_ITEM] as column_value  from  generalassemblies_data_final
union all

select  'findNum' as column_name,[FIND_NUMBER] as column_value  from  generalassemblies_data_final
union all

select  'qty' as column_name,[QUANTITY] as column_value  from  generalassemblies_data_final

)

select *
from q1;

Can anybody please help me out here.

M.Ali · Accepted Answer · 2016-05-31 13:57:06Z

2

Select   ColumnName
       , ColumnValue
FROM 
(
  --<-- Make sure all the columns are converted to the same data type here
     -- This is the only purpose of this sub-query 
    Select CHANGE_NUMBER      
          ,ITEM_NUMBER          
          ,REV
          ,BOM_ITEM
          ,CAST(FIND_NUMBER AS VARCHAR(100)) AS FIND_NUMBER
          ,CAST(QUANTITY    AS VARCHAR(100)) AS QUANTITY
    from TableName --<-- Your Table name here 
)t  
   UNPIVOT (ColumnValue for ColumnName 
           IN (CHANGE_NUMBER , ITEM_NUMBER , REV
               ,BOM_ITEM , FIND_NUMBER , QUANTITY)
            )UP

answered May 31, 2016 at 13:57

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Rabjohn · Accepted Answer · 2016-05-31 13:55:50Z

Your list of UNION ALL's will work provided that the column_values are all converted to the same type (nvarchar?). Beyond that, I'd suggest that you're looking at a presentation-level problem, which is handled with a program that can consume the query result.

I would imagine that any Report designer could work. If you're looking to do this in a 3GL such as VB.NET, you'd just keep your row based query and write out the formatted data:

for each row in someresult
  Console.WriteLine(string.format("Change Number" & vbtab & "{0}", row.ChangeNumber)
  Console.WriteLine(string.format("Item Number" & vbtab & "{0}", row.ItemNumber)
next

I can't give you the precise syntax here, but some mixture of result, output stream and formatting would work.

Kittoes0124 · Accepted Answer · 2016-06-01 12:21:18Z

1

You can also use the APPLY operator to do this sort of thing:

select Transposed.*
from <TableName>
cross apply (values
    (CHANGE_NUMBER)
  , (ITEM_NUMBER)
  , (REV)
  , (BOM_ITEM)
  , (FIND_NUMBER)
  , (QUANTITY)
  , (CHANGE_NUMBER)
  , (ITEM_NUMBER)
  , (REV)
  , (BOM_ITEM)
  , (Cast(FIND_NUMBER as varchar(10)))
  , (Cast(QUANTITY as varchar(10)))
) as Transposed (ChangeNumber, ItemNumber, Revision, BomItem, FindNumber, Quantity);

I prefer this version because I already use APPLY on the regular and find it easier to remember than UNPIVOT. Also, I only aliased the columns using PascalCase to highlight the syntax; feel free to change them to whatever you want.

edited Jun 1, 2016 at 12:21

answered May 31, 2016 at 14:30

Kittoes0124

5,1024 gold badges30 silver badges48 bronze badges

2 Comments

Burhan Khalid Butt Over a year ago

It does not work. The query when executed, prompts error as "Incorrect syntax near the keyword 'as'."

Kittoes0124 Over a year ago

@BurhanKhalidButt Sorry, forgot two parenthesis.

Collectives™ on Stack Overflow

Converting columns into rows against each row

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related