Select 2 column in 2 rows in sql query

Question

I have a data in the following format :

ID          Vendor Name     Company Name

1                 VendorA         CompanyA

2                 VendorB         CompanyB

3                 VendorC         CompanyC

I want in the following format

ID                                     CompanyDetails     
1                                          CompanyA                                

1                                          VendorA                               

2                                          CompanyB

2                                          VendorB 

3                                          CompanyC

3                                           Vendor C

i have used Union all but did not work , please help me write an sql script for this

thank you

Mudassir Hasan · Accepted Answer · 2017-02-07 06:48:30Z

1

SELECT ID,    CompanyDetails
FROM
(
    SELECT ID, VendorName As CompanyDetails, 2 as Tag
    FROM tableName
    UNION ALL
    SELECT ID, CompanyName, 1 As Tag
    FROM tableName
) Z
ORDER BY ID, Tag

edited Feb 7, 2017 at 6:48

answered Feb 7, 2017 at 6:40

Mudassir Hasan

29k21 gold badges104 silver badges135 bronze badges

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

3 Comments

syed wajid ali Over a year ago

i had tried this but Union all alters the sequence. i print Compoany Name then Vendor Name for some rows and reverse the sequence for some other rows

Mudassir Hasan Over a year ago

Check edited answer , the Tag column used in ORDER By will cxause Comapy Name to come first and then VendorName for each ID

Mudassir Hasan Over a year ago

For what rows you want sequence changed ? Specify what dou mean

Madhivanan · Accepted Answer · 2017-02-07 06:44:18Z

0

Try this

SELECT ID, [Vendor Name] from table
union all
SELECT ID, [Company Name] from table
Order by ID

answered Feb 7, 2017 at 6:44

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Comments

Mahesh.K · Accepted Answer · 2017-02-07 06:45:31Z

0

Try this it will help you

select * into #tab from(
select 1 ID, 'VendorA' as [Vendor Name], 'CompanyA' [CompanyName]
union all
select 2, 'VendorB', 'CompanyB'
union all
select 3, 'VendorC', 'CompanyC'
)as a


select id,[Vendor Name] from #tab
union all
select id,[CompanyName] from #tab
order by  ID

answered Feb 7, 2017 at 6:45

Mahesh.K

9016 silver badges16 bronze badges

Comments

Kim · Accepted Answer · 2017-02-07 06:53:03Z

0

You can set an Order to keep the sequence.

SELECT ID, CompanyDetails
FROM (
    SELECT ID, CompanyName AS CompanyDetails, 1 AS Ord
    FROM #table
    UNION
    SELECT ID, VendorName, 2 AS Ord
    FROM #table


) A
ORDER BY ID, Ord

answered Feb 7, 2017 at 6:53

Kim

8258 silver badges25 bronze badges

Comments

mohan111 · Accepted Answer · 2017-02-07 09:42:06Z

0

By using cross apply and NTile also we can achieve

 Select ID,CompanyDetails 
        from (
Select NTILE(6)OVER(PARTITION BY COL ORDER BY COL DESC )ID ,
T.VAL As CompanyDetails 
            from (
select COL,VAL from  #Table1
CROSS APPLY(VALUES 
    ('Vendorname',[Vendor Name]),
    ('CompanyName',[CompanyName]))CS(COL,VAL))T)TT
    ORDER BY ID

edited Feb 7, 2017 at 9:42

answered Feb 7, 2017 at 8:52

mohan111

8,8954 gold badges35 silver badges61 bronze badges

Collectives™ on Stack Overflow

Select 2 column in 2 rows in sql query

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related