1

Using MS SQL 2008, I have created a table that I want to display in horizontal direction instead of vertical direction. Because I do not now how many query results I will get returned I think I have to use the pivot function in combination with dynamic SQL

I already saw some examples with dynamic SQL and pivot, but I could not figure out what my query should look like in order to generate the correct output. Most Pivot examples includes aggregation (AVG, MAX/MIN, Count) functions, but this is not what I want/need. Each row is uniq.

The issue is that the first 3 columns should remain in their original position while the rest should be moved to new (to be generated) columns in the horizontal position.

The starting table looks as below, with all columns as varchar

Article number, 
Article Description,
Gross price,
OEM Customer name,
OEM Article number (alternative OEM article number),
OEM Article description (alternative OEM article description),
OEM price

Using some abbreviations the columnnames could be as below.

Art_nr; Art_descr; Gross_price; OEM_Cust, OEM_Art_nr;OEM_Art_descr;OEM_price 

1234,cup,100,Jones,101234,cup_a,95
1234,cup,100,Daniels,NULL,cup_b,90
1234,cup,100,Williams,NULL,cup_c,85
3456,plate,200,Jones,103456,plate_a,190
3456,plate,200,Daniels,NULL,plate_b,180
3456,plate,200,Williams,NULL,plate_c,170
987,fork,5,Jones,10987,fork_a,4
987,fork,5,Daniels,NULL,fork_b,3
987,fork,5,Williams,NULL,fork_c,2
rest of rows....(more articles but also more customers)

Code to create example table:

CREATE TABLE My_Table(
Item_No VARCHAR(50),
Item_Desc VARCHAR(50),
Item_Price VARCHAR(50),
Cust_Name VARCHAR(50),
Cust_Item_No VARCHAR (50),
Cust_Item_Desc VARCHAR (50),
Cust_Item_Price VARCHAR (50) 
)

INSERT INTO My_Table
(Item_No, Item_Desc, Item_Price, Cust_Name, Cust_Item_No, Cust_Item_Desc, Cust_Item_Price)
VALUES
('1234','cup','100','Jones','101234','cup_a','95'), 
('1234','cup','100','Daniels',NULL,'cup_b','90'),
('1234','cup','100','Williams',NULL,'cup_c','85'), 
('3456','plate','200','Jones','103456','plate_a','190'),
('3456','plate','200','Daniels',NULL,'plate_b','180'),
('3456','plate','200','Williams',NULL,'plate_c','170'),
('987','fork','5','Jones','10987','fork_a','4'),
('987','fork','5','Daniels',NULL,'fork_b','3'),
('987','fork','5','Williams',NULL,'fork_c','2');

This would be the desired table:

Art_nr; Art_descr; Gross_price;OEM_Cust_1,OEM_1_Art_nr;OEM_1_Art_descr;OEM_1_price;OEM_Cust_2, OEM_2_Art_nr;OEM_2_Art_descr;OEM_2_price;OEM_Cust_3, OEM_3_Art_nr;OEM_3_Art_descr;OEM_3_price
1234,cup,100,Jones,101234,cup_a,95,Daniels,NULL,cup_b,90,Williams,NULL,cup_c,85
3456,plate,200,Jones,103456,plate_a,190,Daniels,NULL,plate_b,180,Williams,NULL,plate_c,170
987,fork,5,Jones,10987,fork_a,4,Daniels,NULL,fork_b,3,Williams,NULL,fork_c,2

So far I have come up with this query:

SELECT Item_No, Item_Desc, Item_Price, Cust_Name_1, Cust_Item_No_1, Cust_Item_Desc_1, Cust_Item_Price_1, Cust_Name_2, Cust_Item_No_2, Cust_Item_Desc_2, Cust_Item_Price_2, Cust_Name_3, Cust_Item_No_3, Cust_Item_Desc_3, Cust_Item_Price_3
FROM
    (select Item_No, Item_Desc, Item_Price, Cust_Name, Cust_Item_No, Cust_Item_Desc, Cust_Item_Price
     FROM My_Table) AS Temp_Table
PIVOT
(
  MAX (Cust_Name)
  FOR Cust_Name in(Cust_Name_1, Cust_Item_No_1, Cust_Item_Desc_1, Cust_Item_Price_1, Cust_Name_2, Cust_Item_No_2, Cust_Item_Desc_2, Cust_Item_Price_2, Cust_Name_3, Cust_Item_No_3, Cust_Item_Desc_3, Cust_Item_Price_3)
) AS PivotTable;

The query above is not (yet) dynamical but only valid for the example given, but will function for me as "proof of concept". Unfortunately the results in the new columns are all NULL so the query is not working.

Who would be so kind to show me the query to get to the requested result or give me some clues to improve my own query?

  • 1
    Which RDBMS? Do you know any SQL? – Philᵀᴹ Feb 23 '14 at 23:48
  • I'am using MS SQL 2008. I have used some SQL recently and am still learning. Asked some questions recently that lead me to build up the input table for this question. – Herbiek Feb 24 '14 at 0:14
  • Why do you want to do this? How many customers do you want listed per row? – Zack Feb 24 '14 at 22:32
  • @Zack: I have to get this data in this order to get an easy import for a catalog program that reads this data (in this order). The number of customers is about 50 (as maximum). – Herbiek Feb 25 '14 at 7:12
  • @Phil why did you asked if I knew any SQL? – Herbiek Mar 5 '14 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.