Converting columns into rows in T-SQL (SQL 2005)

Question

Trying to figure out how to do a query to give me the results in a desired way:

Table Cars:

car_id  Make    Model   Color
1   Ford    Focus   Red
2   Ford    Fiesta  Silver
3   Honda   Accord  Silver

Select from cars where car_id = 1

Car_id  1
Make    Ford
Model   Focus
Color   Red

So pretty much taking a column and converting it into a row. Using SQL 2005 (which I am new tat using) as a database.

Thanks

Format text as code where you need line breaks preserved.

Martin Smith
– Martin Smith

2011-09-20 22:30:41 +00:00
Commented Sep 20, 2011 at 22:30 — Martin Smith
– Martin Smith, Commented Sep 20, 2011 at 22:30

t-clausen.dk · Accepted Answer · 2011-09-21 15:50:22Z

2

Here is an easy way:

;with a as
(
  select id, make, model, color
  from cars
  where id = 1
)
select 'Car_id' [Col1], cast(Car_id as varchar(10)) [Col2]
from a
union all
select 'make', make
from a
union all
select 'Model', Model
from a
union all
select 'Color', Color
from a

edited Sep 21, 2011 at 15:50

answered Sep 21, 2011 at 3:58

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

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

1 Comment

Wayne In Yak Over a year ago

I was getting this error message "Conversion failed when converting the varchar value 'Ford' to data type int.", and figured it was making the first column type int. Casting car_id as varchar got it to work for me. Thanks Now to see if I can get this to work while attmepting to join three tables together.

JonH · Accepted Answer · 2011-09-20 22:26:50Z

1

Its simplier than you think, you can concatenate those columns into one column, that one column's value becomes the resulting row:

SELECT CarModel + ' ' + CarMake + ' ' + CarColor AS MyColumn FROM MyTable

answered Sep 20, 2011 at 22:26

JonH

33.2k13 gold badges94 silver badges149 bronze badges

5 Comments

Wayne In Yak Over a year ago

That works if I want a single row returned, but I want a row for each of those columns. So where I would had one row if I filtered by car_id = 1 I actually want to get 4 rows back. Hope that makes sense.

JonH Over a year ago

@Wayne In ML - This doesn't make sense, my solution would work on each current row. Assume you have 4 cars in the table, you would get 4 rows with the car model the car make and the color, for each car.

Wayne In Yak Over a year ago

I tired your query using from table where car_id = 1. I got exactly one row returned. Create table #cars( car_id int, make varchar(10), model varchar(10), color varchar(10) ) insert into #cars values(1,'Ford','Focus','Red') insert into #cars values(2,'Ford','Fiesta','Silver') insert into #cars values(3,'Honda','Accord','Silver') SELECT Model + ' ' + Make + ' ' + Color AS MyColumn FROM #cars where car_id = 1 DROP table #cars

Wayne In Yak Over a year ago

Well the formatting doesn't come across.. But if you take my comment start at Creat etable and copying to the end, pop it into a query window and run it, you'll get one row returned, with the three field combined into that one row.

Wayne In Yak Over a year ago

Just to make it clear, I hope, when I run the query for car_id = 1, I would get one row with two columsn, Make and Ford, a second row with two columns, Model and Fiesta, and a last row with two columns, Color and Red.

RThomas · Accepted Answer · 2012-05-17 06:56:40Z

1

we can write generic solution for any query.

--sample table
SELECT * INTO #cars
FROM (  SELECT 1 car_id, 'Ford' Make, 'Focus' Model,'Red' Color
    UNION all
    SELECT 2 car_id, 'Ford' Make, 'Fiesta' Model,'Silver' Color
    UNION ALL 
    SELECT 3 car_id, 'Honda' Make, 'Accord' Model,'Silver' Color
    ) x

--selected record to #tmp
SELECT * INTO #tmp FROM #cars WHERE car_id=1

--generic solution
DECLARE @sql VARCHAR(max)
SET @sql=''
SELECT @sql=@sql + 'SELECT '''+Name+''' as ColumnName,
    cast(['+Name+'] as varchar(500)) as Value FROM #tmp union all ' 
FROM tempdb.sys.columns 
where  object_id=object_id('tempdb..#tmp')

SET @sql = LEFT(@sql,LEN(@sql)-9)  -- except last 'union all'

EXEC( @sql)

DROP TABLE #tmp,#cars

edited May 17, 2012 at 6:56

RThomas

10.9k2 gold badges50 silver badges65 bronze badges

answered May 17, 2012 at 6:37

HASAN AYDIN

111 bronze badge

1 Comment

RThomas Over a year ago

Welcome to stack overflow. You can format code by putting four spaces before code lines and the engine will take care of the rest. I edited it for you but have to ask... did you really try to put your answer inside a BLINK tag??? That's funny right there.

CruelIO · Accepted Answer · 2011-09-21 12:04:22Z

0

how about

declare @mytable table(car_id int, Make varchar(10), model varchar(10), color varchar(10))

insert @mytable values(1,'Ford','Focus','Red')
insert @mytable values(2,'Ford','Fiesta','Silver')
insert @mytable values(3,'Honda','Accord','Silver')

select 'Car_id ' +convert(varchar, car_id)+char(10)+char(13)+ 
'Make ' + Make + char(10)+char(13)+ 
'Color '+color 
from @mytable
where car_id = 1

answered Sep 21, 2011 at 12:04

CruelIO

18.7k16 gold badges48 silver badges62 bronze badges

1 Comment

Wayne In Yak Over a year ago

Thanks, but gives me the same results as the first suggestion.

Collectives™ on Stack Overflow

Converting columns into rows in T-SQL (SQL 2005)

4 Answers 4

1 Comment

5 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

5 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related