combine sql rows into 1 row

I have a sql 2014 query that returns the last 3 months of customer order history

Looking for a way to combine them into a single row

here is a sample of data

customerid  month   year    orderquantitytotal   orderdiscounttotal  orderamounttotal
1                     11          2017   200                              10                              4000
1                     12          2017   400                              50                              10000
1                     01          2018   100                               0                               1000

What I'm hoping to get as a result is a single row ( due to space constraints it is wrapping here)

customerid  month1   year1    orderquantitytotal1  orderdiscounttotal1    orderamounttotal1   month2  year2  orderquantitytotal2
1                     11          2017       200                              10                                    4000                              12           2017   400

orderdiscounttotal2   orderamounttotal2   month3   year3    orderquantitytotal3   orderdiscounttotal3  orderamounttotal3
400                                 50                                 01             2018     100                               0                                    100


note: the stored proc that will executes this query has input of customer id as parameter so only looking for 1 customer at a time

Looking for some suggestions on the query
johnnyg123Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dustin SaundersDirector of OperationsCommented:
You can join the table to itself to get something like that.  Quick example:
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [customerid]
      ,[month]
      ,[year]
      ,[orderquantitytotal]
      ,[orderdiscounttotal]
      ,[orderamounttotal]
  FROM [Scratch].[dbo].[ee29079280]

  GO

  SELECT o1.customerid, o1.month, o1.year, o1.orderquantitytotal, o1.orderdiscounttotal,o1.orderamounttotal,
  o2.month, o2.year, o2.orderquantitytotal, o2.orderdiscounttotal,o2.orderamounttotal,
  o3.month, o3.year, o3.orderquantitytotal, o3.orderdiscounttotal,o3.orderamounttotal
  FROM ee29079280 o1
  INNER JOIN ee29079280 o2 ON o1.customerid = o2.customerid AND o2.month = 12 AND o2.year = 2017
  INNER JOIN ee29079280 o3 ON o1.customerid = o3.customerid AND o3.month = 1 AND o3.year = 2018
  WHERE o1.month = 11 AND o1.year = 2017

Open in new window


Results:
sql_joinself.png
Now, you can use variables for those joins but what I would do is create a stored procedure where you specify the month/year and it will build the result for you off the past 3 months.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Is this in Access?  If so, take a look at the following article:

https://www.experts-exchange.com/articles/2380/Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

It has code you can drop in and then call the DConcat() function.

Jim.
0
johnnyg123Author Commented:
unfortunately not

 sql 2014 query
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<unfortunately not>>

 Sorry for the post then.   The question got scooped up in my "MS Access" question filter for some reason.    I didn't see the topic but posted anyway thinking there might be a problem with the tagging.

Jim.
0
johnnyg123Author Commented:
no worries....thanks for the response
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
referring to this recent post and you can adapt the similar approach:

Concatenate rows
https://www.experts-exchange.com/questions/29074396/Concatenate-rows.html

example:

if exists(select * from sys.objects where object_id = object_id(N'yourTable') and type in (N'U'))
drop table yourTable
go
    
create table yourTable
(
	customerid int,
	month varchar(2),
	year int,
	orderquantitytotal int,
	orderdiscounttotal int,
	orderamounttotal int
)
insert into yourTable
values
(1, '11', 2017, 200, 10, 4000),
(1, '12', 2017, 400, 50, 10000),
(1, '01', 2018, 100, 0, 1000);


declare @MaxCount int;
set @MaxCount = 3;

declare @SQL nvarchar(max),@i int;
 
set @i = 0;
 
while @i < @MaxCount
begin
    set @i = @i + 1;
   
    set @SQL = coalesce(@SQL + ', ', '') + 'max(case when rn = '+ cast(@i as nvarchar(10))+' then month end) as month' + cast(@i as nvarchar(10));
    set @SQL = coalesce(@SQL + ', ', '') + 'max(case when rn = '+ cast(@i as nvarchar(10))+' then year end) as year' + cast(@i as nvarchar(10));
    set @SQL = coalesce(@SQL + ', ', '') + 'max(case when rn = '+ cast(@i as nvarchar(10))+' then orderquantitytotal end) as orderquantitytotal' + cast(@i as nvarchar(10));
	set @SQL = coalesce(@SQL + ', ', '') + 'max(case when rn = '+ cast(@i as nvarchar(10))+' then orderdiscounttotal end) as orderdiscounttotal' + cast(@i as nvarchar(10));
	set @SQL = coalesce(@SQL + ', ', '') + 'max(case when rn = '+ cast(@i as nvarchar(10))+' then orderamounttotal end) as orderamounttotal' + cast(@i as nvarchar(10));
end

set @SQL = 
	N'select customerid, '+ @SQL + ' ' +
	'from
	(
      select *, row_number() over(partition by customerid order by year, month) as rn
      from yourTable 
    ) d
	group by customerid'
 
print @SQL;
exec (@SQL);

Open in new window

0
Mark WillsTopic AdvisorCommented:
Always wanted a question in here to see if I can unpivot to make a pivot. Thank You :)

with cte as
( select customerid, row_number() over (partition by customerid order by year,month) as RN,[month],[year],[orderquantitytotal],[orderdiscounttotal],[orderamounttotal]
  from yourTable
), upv as
( select customerid,colvalues,colnames + '_'+format(rn,'##') as colnames
  from cte
  unpivot (ColValues for Colnames in ([month],[year],[orderquantitytotal],[orderdiscounttotal],[orderamounttotal]) ) U
) select * 
  from upv
  Pivot (max(colvalues) for colnames in ([month_1],[year_1],[orderquantitytotal_1],[orderdiscounttotal_1],[orderamounttotal_1],
          [month_2],[year_2],[orderquantitytotal_2],[orderdiscounttotal_2],[orderamounttotal_2],
          [month_3],[year_3],[orderquantitytotal_3],[orderdiscounttotal_3],[orderamounttotal_3]))P

Open in new window

It does assume all columns other than customerID are integers....
0
johnnyg123Author Commented:
The suggestions work great as long as there is a record for each month

However, sometimes a customer might not have orders in all 3 months in which case I get an empty record

any suggestions????

Fyi
Mark I really liked your suggestion but unfortunately not all the columns other than customerid are integers
0
Dustin SaundersDirector of OperationsCommented:
In your stored procedure, add in some IF statements.

Here's an example using your test data, might be a better way using CTE but this does the trick and you can ammend it for your SP:

DECLARE @customerid INT, @month INT, @year INT, @monthsBack INT, @n INT, @test INT, @query NVARCHAR(max), @from NVARCHAR(max), @nextYr INT, @nextMo INT
SET @customerid = 1
SET @monthsBack = 3
SET @month = 1
SET @year = 2018

SET @n = 1

SET @query = 'SELECT '

WHILE @n <= @monthsBack
BEGIN
	DECLARE @x NVARCHAR(10)
	SET @x = @n
	IF @n = 1
	BEGIN
		SET @query = @query + ' o' + @x +  '.customerid'
		SET @nextYr = @year
		SET @nextMo = @month
		SET @from = ' FROM ee29079280 o1'
	END
		
	IF EXISTS (SELECT * FROM ee29079280 WHERE [customerid] = @customerid AND [year] = @nextYr AND [month] = @nextMo)
	BEGIN
		SET @query = @query + ', o' + @x + '.month, o' + @x + '.year, o' + @x + '.orderquantitytotal, o' + @x + '.orderdiscounttotal, o' + @x + '.orderamounttotal'
	END

	IF @n > 1 AND EXISTS (SELECT * FROM ee29079280 WHERE [customerid] = @customerid AND [year] = @nextYr AND [month] = @nextMo)
	BEGIN
		SET @from = @from + ' INNER JOIN ee29079280 o' + @x + ' ON o1.customerid = o' + @x + '.customerid AND o' + @x + '.month =' + CAST(@nextMo AS nvarchar(10)) + ' AND o' + @x + '.year =' + CAST(@nextYr AS nvarchar(10))
	END
	
	IF @nextMo > 1
	BEGIN
		SET @nextMo = @nextMo - 1
	END
	ELSE
	BEGIN
		SET @nextMo = 12
		SET @nextYr = @nextYr - 1
	END
	
	SET @n = @n+1
END

SET @query = @query + @from + ' WHERE o1.customerid=' + CAST(@customerid AS NVARCHAR(10)) + ' AND o1.month=' + CAST(@month AS NVARCHAR(10)) + ' AND o1.year=' + CAST(@year AS NVARCHAR(10))

EXEC(@query)

Open in new window


Using customerid = 1, year = 2018, month = 1
query_1.png
Using customerid = 1, year = 2017, month = 12
query_2.png
Using customerid = 1, year = 2017, month = 11
query_3.png
Using customerid = 1, year = 2017, month = 10 (no data)
query_4.png
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
However, sometimes a customer might not have orders in all 3 months in which case I get an empty record
if i understand your requirement correctly...

what you need to do is to create a "base" table and from there we joining with the tables using existing SQL statements.

like:

if exists(select * from sys.objects where object_id = object_id(N'yourTable') and type in (N'U'))
drop table yourTable
go
    
create table yourTable
(
	customerid int,
	month varchar(2),
	year int,
	orderquantitytotal int,
	orderdiscounttotal int,
	orderamounttotal int
)
insert into yourTable
values
(1, '11', 2017, 200, 10, 4000),
(1, '12', 2017, 400, 50, 10000),
(1, '01', 2018, 100, 0, 1000),
(2, '12', 2017, 888, 8, 8888),
(3, '01', 2018, 777, 7, 7777),
(3, '02', 2018, 778, 78, 7778);

declare @startDate datetime, @Monthseries int
set @startDate = '2017-11-01'
set @Monthseries = 12


declare @MaxCount int;
set @MaxCount = 12;

declare @SQL nvarchar(max),@i int;
 
set @i = 0;
 
while @i < @MaxCount
begin
    set @i = @i + 1;
   
    set @SQL = coalesce(@SQL + ', ', '') + 'max(case when rn = '+ cast(@i as nvarchar(10))+' then month end) as month' + cast(@i as nvarchar(10));
    set @SQL = coalesce(@SQL + ', ', '') + 'max(case when rn = '+ cast(@i as nvarchar(10))+' then year end) as year' + cast(@i as nvarchar(10));
    set @SQL = coalesce(@SQL + ', ', '') + 'max(case when rn = '+ cast(@i as nvarchar(10))+' then orderquantitytotal end) as orderquantitytotal' + cast(@i as nvarchar(10));
	set @SQL = coalesce(@SQL + ', ', '') + 'max(case when rn = '+ cast(@i as nvarchar(10))+' then orderdiscounttotal end) as orderdiscounttotal' + cast(@i as nvarchar(10));
	set @SQL = coalesce(@SQL + ', ', '') + 'max(case when rn = '+ cast(@i as nvarchar(10))+' then orderamounttotal end) as orderamounttotal' + cast(@i as nvarchar(10));
end

set @SQL = 
	N'
	;with cte as
	(
		select dateadd(mm, number, '''+convert(varchar(8),@startDate,112)+''') dseries
		from master.dbo.spt_values
		where type = ''P'' and number <= '+cast(@Monthseries as varchar(2))+'-1
	), cte2 as
	(
		select row_number() over(partition by a.customerid order by year(b.dseries), month(b.dseries)) rn,
		a.customerid, year(b.dseries) yr, month(b.dseries) mth
		from (select customerid from yourTable group by customerid) a, cte b
	)
	, cte3 as
	(
		select 
		a.yr, a.mth, a.rn, b.*
		from cte2 a left join yourTable b
		on a.customerid = b.customerid and a.yr = b.year and a.mth = cast(b.month as int)
	)
	select customerid, '+ @SQL + ' ' +
	'from cte3
	where customerid is not null
    group by customerid'
 
print cast(@SQL as ntext);
exec (@SQL);

Open in new window

0
Mark WillsTopic AdvisorCommented:
Not all columns have to be integers, only those to be unpivoted need to be same data type. I assumed integer at the time...

Had assumed that  month   year    orderquantitytotal   orderdiscounttotal  orderamounttotal were all going to be numeric - can easily accommodate if you can tell me the datatypes....

>> However, sometimes a customer might not have orders in all 3 months in which case I get an empty record

Do you want it to show as empty or suppress ?


And to show what I mean by same datatype, lets use varchar as an example....
with cte as
( select customerid, row_number() over (partition by customerid order by year,month) as RN
        ,convert(varchar(20),[month]) as [Month]
		,convert(varchar(20),[year]) as [year]
		,convert(varchar(20),[orderquantitytotal]) as [orderquantitytotal]
		,convert(varchar(20),[orderdiscounttotal]) as [orderdiscounttotal]
		,convert(varchar(20),[orderamounttotal]) as [orderamounttotal]
  from yourTable
), upv as
( select customerid,colvalues,colnames + '_'+format(rn,'##') as colnames
  from cte
  unpivot (ColValues for Colnames in ([month],[year],[orderquantitytotal],[orderdiscounttotal],[orderamounttotal]) ) U
) select * 
  from upv
  Pivot (max(colvalues) for colnames in ([month_1],[year_1],[orderquantitytotal_1],[orderdiscounttotal_1],[orderamounttotal_1],
          [month_2],[year_2],[orderquantitytotal_2],[orderdiscounttotal_2],[orderamounttotal_2],
          [month_3],[year_3],[orderquantitytotal_3],[orderdiscounttotal_3],[orderamounttotal_3]))P

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnnyg123Author Commented:
Thanks so much for all the help!

Hope everyone is ok that I assigned points to all that took time to respond
0
Mark WillsTopic AdvisorCommented:
A pleasure and thank you for your question and patience. Much appreciated and enjoyed the opportunity.

Cheers,
Mark Wills
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.