Need vertical table as horizontal table in SQL

Question

Need vertical table as horizontal table in SQL

kbhanu15

SSCarpal Tunnel

Points: 4498
More actions
December 4, 2017 at 5:20 am

#326727

Hi Team,
can you please help me to get
create table company
(
name varchar(100)
)
insert into company
select 'IBM'
UNION ALL
select 'Goolge'
UNION ALL
select 'Microsoft'
UNION ALL
select 'apple'
select * from company
Input :

Name
IBM
Goolge
Microsoft
apple
output:

company1 company2 company3 company4
IBM Goolge Microsoft apple

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

aaron.reese SSChampion Points: 13498 More actions · Answer 1

The example you have given is clearly not the business problem you are trying to solve. SQL does not give you tables, it gives you data sets

Depending on exactly what you are trying to do I would suggest researching STUFF...FOR XML and PIVOT

If you are trying to render an output for embedding an HTML table in a web page or email then STUFF...FOR XML is almost certainly the way to go. If you are outputting information so that you can copy-paste the results to Excel then PIVOT is probably a more appropriate solution. Alternatively for Excel you could just output the rows and then create a pivot table in Excel.

Give us some more context around the business problem and we can probably assist with a more appropriate answer.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

aaron.reese - Monday, December 4, 2017 5:24 AM
SQL does not give you tables, it gives you data sets

Not sure I agree with that. For example, if the code you use to create a data set contains a SELECT/INTO, it will, in fact, create a table that looks like the data set.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

aaron.reese SSChampion Points: 13498 More actions · Answer 3

aaron.reese

SSChampion

Points: 13498

December 4, 2017 at 7:32 am

#1970526

pedant 🙂

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

aaron.reese - Monday, December 4, 2017 7:32 AM
pedant 🙂

😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

kbhanu15 - Monday, December 4, 2017 5:20 AM
Hi Team,
can you please help me to get
create table company
(
name varchar(100)
)
insert into company
select 'IBM'
UNION ALL
select 'Goolge'
UNION ALL
select 'Microsoft'
UNION ALL
select 'apple'
select * from company
Input :
Name
IBM
Goolge
Microsoft
apple
output:
company1 company2 company3 company4
IBM Goolge Microsoft apple

Thanks for posting readily consumable code/data. It'll help but I have a question. What does your real table look like and do you need to pivot more than one group of rows where the result set will have multiple rows for the 4 companies? Also, will you ever have more than just 4 companies?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)