0

I have a data in this format 

Cust_ID    Order_Bname
----------------------
  1          Samsung
  1          Apple
  1          pixel
  2          Apple
  3          pixel
  3          LG

I want output in this format:

Cust_ID     Order_Bname1    order_Bname2    Order_Bname3
-----------------------------------------------------------
 1           Samsung            Apple         Pixel
 2           Apple              Null          Null
 3           Pixel              LG            null

Basically I want single row for each customer.

I have tried FOR XML PATH like this 

SELECT Cust_ID + ',' + Order_Bname 
FROM temp1
FOR XML PATH ('')

This returns output in this format

Cust_ID      Order
---------------------------------
 1           Apple,Samsung,Pixel
 2           apple
 3           pixel,LG

Is there a way to achieve this? I am not able to figure out how

Update 2:

WITH CTE_count
as
(
  select Customer_ID as ID,Brand_Name +'-'+ cast([Sale_Amount] as nvarchar) 
  as 
  brandname,ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Customer_ID)
  AS lineNumber
  from dbo.sheet1
  group by Customer_ID,Brand_Name,[Sale_Amount]
)
 SELECT ID, brandname,lineNumber
 FROM CTE_count

using this I have got this format:

enter image description here

Now how do I create column dynamically from row number and put brand name in to them

PriyalChaudhari
  • 363
  • 1
  • 7
  • 23
  • I dont want my row value in column name hence not a pivot question – PriyalChaudhari Jul 26 '18 at 04:22
  • 1
    Priyal, I can't write an answer on a closed question, but you need to start by creating a RowNo partitioned by ID in a subquery. This way you know what column each row should be in. Then group by ID for the main query, and join back to the subquery once for each Order1, order2 column to get the column data. – DancingFool Jul 26 '18 at 04:36
  • See https://stackoverflow.com/questions/51201359/is-it-possible-to-convert-rows-to-a-variable-number-of-columns-in-t-sql for a similar example – DancingFool Jul 26 '18 at 04:46
  • @DancingFool i did this part `SELECT Customer_ID,ROW_NUMBER() OVER (PARTITION BY Customer_ID ORDER BY Customer_ID) AS lineNumber FROM dbo.Sheet1` I didnt got the next part you said – PriyalChaudhari Jul 26 '18 at 04:59
  • @DancingFool i have updated the question with your suggestion.Please help how do i move forward – PriyalChaudhari Jul 26 '18 at 05:31
  • If you look at the link I gave you, there is an example there that is very similar. It was doing multiple groupings, but the theory is the same. – DancingFool Jul 27 '18 at 00:01

0 Answers0