CODEWITHSUNDEEP
sql-server
Burhan Khalid Butt
Burhan Khalid Butt

Reputation: 275

Converting columns into rows against each row

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.

Upvotes: 2

Views: 94

Answers (3)

Kittoes0124
Kittoes0124

Reputation: 5080

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.

Upvotes: 1

M.Ali
M.Ali

Reputation: 69494

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

Upvotes: 2

Mark Rabjohn
Mark Rabjohn

Reputation: 1713

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.

Upvotes: 1

Related Questions