table columns data in one row sql query

Question

ID  Model   Color
1   BMW     Red
2   BMW     Green
3   BMW     Yellow
4   BMW     Black
5   Golf    Red
6   Golf    Green
7   Golf    Yellow
8   Golf    Black

I have model of the car and colors as table and i want to make it in one row as this. This will display in datagrid in c#

Model   Color1  Color2  Color3  Color4
BMW     Red     Green   Yellow  Black
Golf    Red     Green   Yellow  Black

--Edit I have a car model that has four colors. I want this to show in one row. new name columns can be anything can be ID but ID can be changed.

So it can be like...

Model   1        2      3       4
BMW     Red     Green   Yellow  Black
Golf    Red     Green   Yellow  Black

What's your question? What are you trying to do, and what have you got so far? — chwcz7F
– chwcz7F, Commented Aug 21, 2014 at 13:36
I want one row as model but adding four columns and make this which will be distributed in color. — user2841795
– user2841795, Commented Aug 21, 2014 at 13:49

Apothis · Accepted Answer · 2014-08-21 13:38:12Z

1

I believe PIVOT will assist you:

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

answered Aug 21, 2014 at 13:38

Apothis

4263 silver badges7 bronze badges

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

Comments

Kevin Hogg · Accepted Answer · 2014-08-22 07:49:00Z

0

SELECT model, group_concat(DISTINCT color)
  FROM table
 GROUP BY model

It will give 2 recodes. model name and string of color model. Then you need to process in C# to get individual colors.

edited Aug 22, 2014 at 7:49

Kevin Hogg

1,78125 silver badges34 bronze badges

answered Aug 21, 2014 at 13:40

kayra

2183 silver badges10 bronze badges

Comments

Kevin Hogg · Accepted Answer · 2014-08-22 07:49:30Z

0

I'd start with:

SELECT COUNT(*), Model, Color
  FROM Table
 GROUP BY Model, Color

and then use the count it returns to construct your table.

edited Aug 22, 2014 at 7:49

Kevin Hogg

1,78125 silver badges34 bronze badges

answered Aug 21, 2014 at 13:40

chiastic-security

20.5k4 gold badges43 silver badges70 bronze badges

2 Comments

jpw Over a year ago

How would this approach help to achieve the desired result?

chiastic-security Over a year ago

@jpw I think the variety of answers given suggests that no one is quite clear on what the desired result is :)

Kevin Hogg · Accepted Answer · 2014-08-22 07:51:39Z

0

SELECT MODEL, [Red], [Green], [Yellow], [Black]
  FROM (
       SELECT model, color
         FROM tblCartable
        WHERE color IN ('Red', 'black', 'yellow', 'green')
       ) x
 PIVOT first(model) FOR color IN ([Red], [Green], [Yellow], [Black])) p

edited Aug 22, 2014 at 7:51

Kevin Hogg

1,78125 silver badges34 bronze badges

answered Aug 21, 2014 at 14:07

Hlin

1344 bronze badges

Comments

Kevin Hogg · Accepted Answer · 2014-08-22 07:57:01Z

0

SELECT *
  FROM (SELECT Model, Color FROM TableName) as SourceT
 PIVOT (MAX(Color) FOR Color IN ([Red], [Green], [Yellow], [Black])) as Pvt

edited Aug 22, 2014 at 7:57

Kevin Hogg

1,78125 silver badges34 bronze badges

answered Aug 21, 2014 at 14:05

huMpty duMpty

14.5k14 gold badges64 silver badges102 bronze badges

Collectives™ on Stack Overflow

table columns data in one row sql query

5 Answers 5

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related