1

I have created a query on my table like this :

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;

when I run this I am getting this output: enter image description here

Now How do I get 1 row per customer ID like:

cust_id order1 order2 order3 order4 100000443 AMANA-71.31 AMANA-104.4 AMANA-109.89 AMANA-104.4

How do I modify my above query

I want to do this for every ID the row is just given as example

PriyalChaudhari
  • 363
  • 1
  • 7
  • 23

2 Answers2

2

Using your Cte, do

...
Select cust_id,
  max(when lineNumber=1 then brandname end) order1,
  max(when lineNumber=2 then brandname end) order2,
  max(when lineNumber=3 then brandname end) order3,
  max(when lineNumber=4 then brandname end) order4
From Cte_count group by cust_id, brandname
Carsten Massmann
  • 26,510
  • 2
  • 22
  • 43
  • but my every Id has different number of orders I just gave those 4 as example I want to do that for every ID – PriyalChaudhari Jul 26 '18 at 05:53
  • You will have to "hard code" the maximum number of columns you want to show in your select statement. If there should be fewer items to display then the last column(s) will remain empty. So,feel free to add a few more according to my pattern. – Carsten Massmann Jul 26 '18 at 05:56
  • I have added the sc in your solution. the results are now split but I still get result in different rows just that if order1 value present then order 2,3,4, are NULL for 4 order customer . In next row order2 is filled – PriyalChaudhari Jul 26 '18 at 06:06
  • I got it I was doing wrong group by thank you. Do not approve the edits I made. I tried to remove them but I was not able to – PriyalChaudhari Jul 26 '18 at 06:12
0

You need to write Dynamic Pivot Query to achieve your result.
Reference Link:

Convert Rows to columns using 'Pivot' in SQL Server

JERRY
  • 1,165
  • 1
  • 8
  • 22