Bring similar multiple rows of a column into single row using SQL

Question

I want to Table name: Master_Vendors

Vendor_ID   Subvendor_id Subvendor_type Name   City State
1           abc           1              Johnny  York   MN
1           xyz           2              Meera   Birmingham NY
1           gef           3              Gaurav  Cochin NY
2           aaa           1              Laila   Lima   MA
2           bbb           2              Zebo    Reno   SC
2           ccc           3              Gina    Pitts  NY

I want one row per Vendor_ID and i cannot use any aggregations

Vendor_ID  Subvendor_id_1 Name_1 City_1 State_1 Subvendor_id_2 Name_2  City_2     State_2 
1           abc           Johnny  York   NY      xyz           Meera   Birmingham  NY
2           aaa           Laila    Lima  MA      bbb           Zebo    Reno        SC

since i cannot do aggregations i cannot use PIVOT; i have never used CTE's before can we acheive this using CTE?

hello and welcome to stackoverflow! while we would love to help, we aren't here to do your work for. Please provide some examples of what you have tried. — ZeRaTuL_jF, Nov 14 '14 at 04:16
You are trying to `pivot` your results. Lots of examples on SO for this already. — sgeddes, Nov 14 '14 at 04:18
SO is not a code writing service. Try to solve the problem and come back to us if you get stuck somewhere. — Raj, Nov 14 '14 at 04:24
Check this link http://stackoverflow.com/questions/10428993/understanding-pivot-function-in-t-sql — Pரதீப், Nov 14 '14 at 05:11
are you limited to only 3 subvendors per vendor? If not, then I would seriously consider re-visiting your actual requirement.. — StevieG, Nov 17 '14 at 18:04
It actually has 4 subvendors per vendor, it can range between 1-4 but not more than 4 — chandi, Nov 17 '14 at 19:37

score 0 · Answer 1 · answered Nov 14 '14 at 04:44

You can do that by using the PIVOT function but only if you use MS SQL Server 2008 or Higher, Or simply use the CASE WHEN Statement as below:

SELECT Vendor_ID    as 'Vendor_ID ',
       case when Location_ID =1 then Location_ID END as 'Location_ID_1 ' 
      ,case when City ='York' OR City ='Lima' then City END as 'City_1 ' 
      ,case when [State]='MN'  OR [State]='NA' then [State] END as 'State_1' 
      ,case when Location_ID =2 then Location_ID END as 'Location_ID_2 ' 
      ,case when City ='Birmingham' OR City ='Reno'  then City END as 'City_2 ' 
      ,case when [State]='NY' OR [State]='SC' then [State] END as 'State_2 ' 
      ,case when Location_ID =3 then Location_ID END as 'Location_ID_3 ' 
      ,case when City ='Cochin' then City END as 'City_3  ' 
      ,case when [State]='NY'  then [State] END as 'State_3 ' 
FROM Master_Vendors

I am using Sqlserver2008;I changed my question, please take a look at it; the case statement doesnt result in a single row. — chandi, Nov 17 '14 at 18:02

score 0 · Accepted Answer · answered Nov 17 '14 at 18:56

I didn't put in all the fields just enough so you can see the technique. Note this only works when you know in advance how many subvendors you want to show in the query:

select a.vendor_id , a.subvendor_id as Subvendor_id 1, a. Name as name_1, b.subvendor_id as Subvendor_id 2, b. Name as name_1, c.subvendor_id as Subvendor_id 3, a. Name as name_3
From Master_Vendors a
left join Master_Vendors b on a.vendor_id = b.vendor_id and  b.subvendor_type = 2
left join Master_Vendors c on a.vendor_id = c.vendor_id  and  c.subvendor_type = 3
where a.subvendor_type = 1

If every record would have all three subvendors then you can use inner joins.

This is actually working, Thank you so much! I actually kept the left join as is because the subvendors per vendor can range between 1 and 4 — chandi, Nov 17 '14 at 19:38

Bring similar multiple rows of a column into single row using SQL

2 Answers2