SQL that will turn the rows in to columnsin date format

chandan m
chandan m used Ask the Experts™
on
Hi All,

I have an output with list of account # and there are list mails that are mailed to each account that are displayed in rows, For example below is the output i have,

AccountNo         Letter Date
987654                 1/1/2017
987654                  1/10/2017
987654                   1/21/2017
987654                   01/22/2017
987654                   02/21/2017
987654                    03/2/2017

What I’m looking for is some SQL that will turn the rows in to columns, so there is only one row per Account number and number the LetterDate in order of when they were mailed.

So something like this:
AccountNo        Letter 1       Letter 2         Letter 3         Letter 4         Letter 5           Letter 6
987654             1/1/2017    1/10/2017     1/21/2017      02/4/2017     2/17/2017     3/3/2017

Can anybody help me with the solution.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
Look at the solution of the following question, https://www.experts-exchange.com/questions/29010086/In-sql-how-to-roll-up-multiple-rows-to-only-one-row.html

Similar to your situation, you would need to order by AccountNumber , date to add the letter1........letters...

Author

Commented:
No that's not working , I am working on date format that need to be displayed as letter one. Can i get a query so i could modify accordingly for the solution.

Commented:
Try this -

create table dbo.tbl_Letters(
Letter_Id			int,
Account_Number      int,
Letter_Date			date
);


insert into dbo.tbl_Letters values (1, 987654, '1/1/2017');
insert into dbo.tbl_Letters values (2, 987654, '1/10/2017');
insert into dbo.tbl_Letters values (3, 987654, '1/21/2017');
insert into dbo.tbl_Letters values (4, 987654, '01/22/2017');



DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(l.Letter_Id) 
            FROM tbl_Letters l
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = ' WITH DATA AS (
					select TOP (100) PERCENT l.letter_date, l.account_number, l.letter_Id
					from dbo.tbl_Letters l
					order by l.letter_id
				) 
				SELECT distinct account_number, ' + @cols + ' from DATA x
				pivot 
				(
					 max(x.letter_date)
					for x.letter_id in (' + @cols + ')
				) p'
				


execute(@query)

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
convert/cast can be used to switch formatting which was not included in your question. to convert/cast letterdate column to datetime format....
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql
Distinguished Expert 2017

Commented:
order by convert(datetime,LetterDate,101) will change it to datetime format for purposes of sorting

Ref the example in the link that deals with pivoting your table, use order by convert(datetime,LetterDate,101) in both section that deals with adding a third column on which the table data will be pivoted to get the resulting table you outlined.
Developer Analyst
Commented:
another way using PIVOT

drop table #Letters
drop table #MyData
drop table #MyDataNo

-- create table to hold the field names you wish to use
Create table #Letters(
iLetter int,
sLetter varchar(15)
)

insert into #Letters (iLetter,sLetter) values (1,'Letter 1')
insert into #Letters (iLetter,sLetter) values (2,'Letter 2')
insert into #Letters (iLetter,sLetter) values (3,'Letter 3')
insert into #Letters (iLetter,sLetter) values (4,'Letter 4')
insert into #Letters (iLetter,sLetter) values (5,'Letter 5')
insert into #Letters (iLetter,sLetter) values (6,'Letter 6')

-- Create table with sample data
Create table #MyData (
AccountNo varchar(10),
LetterDate datetime
)

-- Insert rows (note that sorting is not in date order :)
insert into #MyData (AccountNo,LetterDate) values ('987654','1/1/2017')
insert into #MyData (AccountNo,LetterDate) values ('987654','1/10/2017')
insert into #MyData (AccountNo,LetterDate) values ('987654','1/21/2017')
insert into #MyData (AccountNo,LetterDate) values ('987654','01/22/2017')
insert into #MyData (AccountNo,LetterDate) values ('987654','03/2/2017')
insert into #MyData (AccountNo,LetterDate) values ('987654','02/21/2017')

--Add Row Numbers in ascending order of date field
select * INTO #MyDataNo
from 
(SELECT *
  FROM (
    SELECT *, (
      SELECT COUNT(*)
        FROM #MyData AS counter
      WHERE counter.AccountNo = #MyData.AccountNo
        AND  counter.LetterDate <= #MyData.LetterDate
      ) AS rowNumber
    FROM #MyData
  )  r1
  ) G1

-- Display as pivot
select AccountNo,
       convert(varchar(10),[Letter 1],120) as [Letter 1],
	   convert(varchar(10),[Letter 2],120) as [Letter 2],
	   convert(varchar(10),[Letter 3],120) as [Letter 3],
	   convert(varchar(10),[Letter 4],120) as [Letter 4],
	   convert(varchar(10),[Letter 5],120) as [Letter 5],
	   convert(varchar(10),[Letter 6],120) as [Letter 6]
from
(select r2.sLetter,m.AccountNo,m.LetterDate from
#MyDataNo m
cross apply #Letters r2
where iLetter = rowNumber) AS SOURCETABLE
PIVOT
(max(letterdate) FOR sLetter IN([Letter 1],[Letter 2],[Letter 3],[Letter 4],[Letter 5],[Letter 6])
) AS PIVOTTABLE

Open in new window


output

Author

Commented:
I have already table which is  a single table and there are accountno and list of letter date for different account #, Which is currently displaying in columns for each account #.

I need to modify column to rows so for each account # it displays how many letters have been gone. In the below format,

AccountNo        Letter 1       Letter 2         Letter 3         Letter 4         Letter 5           Letter 6
987654             1/1/2017    1/10/2017     1/21/2017      02/4/2017     2/17/2017     3/3/2017
562134              2/1/2017    3/1/2017
253641              1/3/2017     3/2/2017     1/22/2017.

I tried with following query,

select Accountno,
 convert(varchar(10),[letterdate],120) as [Letter 1],
  convert(varchar(10),[letterdate],120) as [Letter 2],
         convert(varchar(10),[letterdate],120) as [Letter 3],
         convert(varchar(10),[letterdate],120) as [Letter 4],
         convert(varchar(10),[letterdate],120) as [Letter 5],
         convert(varchar(10),[letterdate],120) as [Letter 6]
 from
  LastLetter

Result is showing
AccountNo      Letter 1      Letter 2      Letter 3      Letter 4      Letter 5      Letter 6
1000826      2001-12-09      2001-12-09      2001-12-09      2001-12-09      2001-12-09      2001-12-09
1000826      2002-07-30      2002-07-30      2002-07-30      2002-07-30      2002-07-30      2002-07-30
1000826      2002-08-29      2002-08-29      2002-08-29      2002-08-29      2002-08-29      2002-08-29

Actually i need the output for each account # the # of letters that is sent is displaying in column the same i want it in rows as letter 1, letter 2 and so on....
Distinguished Expert 2017

Commented:
The short and quick answer is to convert a data set such as yours to a single row is to first add the column on which it will be pivoted

If a user has 18, you have to include 18 entries in the method you chose.

Jeff provided the example that adds the column on which the table can be pivoted, you need to add the maximum number of entries to the pivot section which defines the column headings. If letter1-letter18

Author

Commented:
DebtorNo      LetterDate
0123456789      2002-05-15
1000826      2001-12-09
1000826      2002-07-30
1000826      2002-08-29
1000888      2003-07-21
1000888      2003-09-09
1000888      2002-04-22
1000888      2003-07-25
1000888      2003-10-10

I need above output to convert in rows, Please help me with the query as i am very new to PIVOT plz tell me how to write the query.
The list of letter date to be converted into Letter 1-Letter 6 for each debtor no in rows format.

Author

Commented:
The data i am working is very huge and i need a query that displays in row format for each debtor # the letter date should display as letter 1 ,letter 2 and so on.

Please write me query as i am very new to it.

Commented:
Did you try using the query I posted?
Distinguished Expert 2017

Commented:
Run the query
select top 1 AccountNo,count(AccountNo) from LastLetter group by AccountNo order by count(AccountNo) desc
The response will tell you how many columns you need to reference in the pivot (bottom section of the example Jeff provided that currently has 6.)
Line 61: max(letterdate) FOR sLetter IN([Letter 1],[Letter 2],[Letter 3],[Letter 4],[Letter 5],[Letter 6]..[Letter max count)
Jeff DarlingDeveloper Analyst

Commented:
ok, here is a more simplified version without creating temp tables.  

sample data
Create table #MyData (
AccountNo varchar(10),
LetterDate datetime
)

insert into #MyData (AccountNo,LetterDate) values ('0123456789','2002-05-15')
insert into #MyData (AccountNo,LetterDate) values ('1000826','2001-12-09')
insert into #MyData (AccountNo,LetterDate) values ('1000826','2002-07-30')
insert into #MyData (AccountNo,LetterDate) values ('1000826','2002-08-29')
insert into #MyData (AccountNo,LetterDate) values ('1000888','2003-07-21')
insert into #MyData (AccountNo,LetterDate) values ('1000888','2003-09-09')
insert into #MyData (AccountNo,LetterDate) values ('1000888','2002-04-22')
insert into #MyData (AccountNo,LetterDate) values ('1000888','2003-07-25')
insert into #MyData (AccountNo,LetterDate) values ('1000888','2003-10-10')

Open in new window



select *
from
(select r2.sLetter,m.AccountNo,m.LetterDate from
(SELECT *
  FROM (
    SELECT *, (
      SELECT COUNT(*)
        FROM #MyData AS counter
      WHERE counter.AccountNo = #MyData.AccountNo
        AND  counter.LetterDate <= #MyData.LetterDate
      ) AS rowNumber
    FROM #MyData
  ) G1) m
cross apply (
select 1 as iLetter,'letter 1' as sLetter 
union 
select 2 as iletter,'letter 2' as sletter 
union 
select 3 as iletter,'letter 3' as sletter 
union 
select 4 as iletter,'letter 4' as sletter
union 
select 5 as iletter,'letter 5' as sletter
union 
select 6 as iletter,'letter 6' as sletter
)  r2
where iLetter = rowNumber) AS SOURCETABLE
PIVOT
(max(letterdate) FOR sLetter IN("Letter 1","Letter 2","Letter 3","Letter 4","Letter 5","Letter 6")
) AS PIVOTTABLE

Open in new window


 sample

Author

Commented:
Query works fine but can you explain me the query functions,
Jeff DarlingDeveloper Analyst

Commented:
I just noticed I had an extra subquery in there that isn't needed.  It gives the same result.

select *
from
(select r2.sLetter,m.AccountNo,m.LetterDate from
(
    SELECT *, (
      SELECT COUNT(*)
        FROM #MyData AS counter
      WHERE counter.AccountNo = #MyData.AccountNo
        AND  counter.LetterDate <= #MyData.LetterDate
      ) AS rowNumber
    FROM #MyData
 ) m
cross apply (
select 1 as iLetter,'letter 1' as sLetter 
union 
select 2 as iletter,'letter 2' as sletter 
union 
select 3 as iletter,'letter 3' as sletter 
union 
select 4 as iletter,'letter 4' as sletter
union 
select 5 as iletter,'letter 5' as sletter
union 
select 6 as iletter,'letter 6' as sletter
)  r2
where iLetter = rowNumber) AS SOURCETABLE
PIVOT
(max(letterdate) FOR sLetter IN("Letter 1","Letter 2","Letter 3","Letter 4","Letter 5","Letter 6")
) AS PIVOTTABLE

Open in new window


Subquery to get the row number in date order.

 SELECT *, (
      SELECT COUNT(*)
        FROM #MyData AS counter
      WHERE counter.AccountNo = #MyData.AccountNo
        AND  counter.LetterDate <= #MyData.LetterDate
      ) AS rowNumber
    FROM #MyData

Open in new window


sub01
Then union query builds the table of field.

select 1 as iLetter,'letter 1' as sLetter 
union 
select 2 as iletter,'letter 2' as sletter 
union 
select 3 as iletter,'letter 3' as sletter 
union 
select 4 as iletter,'letter 4' as sletter
union 
select 5 as iletter,'letter 5' as sletter
union 
select 6 as iletter,'letter 6' as sletter

Open in new window


sub02
The Cross Apply query puts the Letter number which is used for the pivot.

select *
from
(select r2.sLetter,m.AccountNo,m.LetterDate from
(
    SELECT *, (
      SELECT COUNT(*)
        FROM #MyData AS counter
      WHERE counter.AccountNo = #MyData.AccountNo
        AND  counter.LetterDate <= #MyData.LetterDate
      ) AS rowNumber
    FROM #MyData
 ) m
cross apply (
select 1 as iLetter,'letter 1' as sLetter 
union 
select 2 as iletter,'letter 2' as sletter 
union 
select 3 as iletter,'letter 3' as sletter 
union 
select 4 as iletter,'letter 4' as sletter
union 
select 5 as iletter,'letter 5' as sletter
union 
select 6 as iletter,'letter 6' as sletter
)  r2
where iLetter = rowNumber) AS SOURCETABLE

Open in new window


sub03

Author

Commented:
Best solution helped me a lot

Author

Commented:
can you send me a link where i can learn more about pivot and understand more on it

Author

Commented:
is there any other way other than pivot function, can we display it without pivot function
Distinguished Expert 2017

Commented:
you would have to manually group by AccountNo and then have the others listed as columns.
The other option is to output the data to test versus a table....

select AccountNo, COALESCE,("Letter Date") from LastLetter group by AccountNo

Author

Commented:
How to get a single account no and display it in column with same query,

Author

Commented:
select *
from
(select r2.sLetter,m.AccountNo,m.LetterDate from
(
    SELECT *, (
      SELECT COUNT(*)
        FROM #MyData AS counter
      WHERE counter.AccountNo = #MyData.AccountNo
        AND  counter.LetterDate <= #MyData.LetterDate
      ) AS rowNumber
    FROM #MyData
 ) m
cross apply (
select 1 as iLetter,'letter 1' as sLetter
union
select 2 as iletter,'letter 2' as sletter
union
select 3 as iletter,'letter 3' as sletter
union
select 4 as iletter,'letter 4' as sletter
union
select 5 as iletter,'letter 5' as sletter
union
select 6 as iletter,'letter 6' as sletter
)  r2
where iLetter = rowNumber) AS SOURCETABLE

Author

Commented:
i need to check for single account # to display the letter 1, letter 2 and so on in column

Author

Commented:
can i have a query for the same output without pivot function in sql
Jeff DarlingDeveloper Analyst

Commented:
Here is a  inefficient way using brute force to peel away each Letter, but maybe not as bad if you only go after 1 account number

Sample with where clause to only get data for 1000888

Create table #MyDataRows (
AccountNo varchar(10),
LetterDate datetime,
rowNumber int
)

INSERT INTO #MyDataRows (AccountNo, LetterDate,rowNumber) 
SELECT *, (
      SELECT COUNT(*)
        FROM #MyData AS counter
      WHERE counter.AccountNo = #MyData.AccountNo
        AND  counter.LetterDate <= #MyData.LetterDate
      ) AS rowNumber
    FROM #MyData
	where accountno='1000888'

Create table #MyPivotData (
AccountNo varchar(10),
LetterDate1 datetime,
LetterDate2 datetime,
LetterDate3 datetime,
LetterDate4 datetime,
LetterDate5 datetime,
LetterDate6 datetime
)

-- LETTER 1
INSERT INTO #MyPivotData (AccountNo, LetterDate1)
SELECT AccountNo, LetterDate from (SELECT  AccountNo,LetterDate FROM #MyDataRows WHERE RowNumber=1) G2

-- LETTER 2
UPDATE #MyPivotData SET #MyPivotData.LetterDate2 = G3.LetterDate FROM #MyPivotData PD
INNER JOIN (SELECT  AccountNo,LetterDate FROM  #MyDataRows WHERE RowNumber=2) G3
ON PD.AccountNo = G3.AccountNo

-- LETTER 3
UPDATE #MyPivotData SET #MyPivotData.LetterDate3 = G3.LetterDate FROM #MyPivotData PD
INNER JOIN (SELECT  AccountNo,LetterDate FROM  #MyDataRows WHERE RowNumber=3) G3
ON PD.AccountNo = G3.AccountNo

-- LETTER 4
UPDATE #MyPivotData SET #MyPivotData.LetterDate4 = G3.LetterDate FROM #MyPivotData PD
INNER JOIN (SELECT  AccountNo,LetterDate FROM  #MyDataRows WHERE RowNumber=4) G3
ON PD.AccountNo = G3.AccountNo

-- LETTER 5
UPDATE #MyPivotData SET #MyPivotData.LetterDate5 = G3.LetterDate FROM #MyPivotData PD
INNER JOIN (SELECT  AccountNo,LetterDate FROM  #MyDataRows WHERE RowNumber=5) G3
ON PD.AccountNo = G3.AccountNo

-- LETTER 6
UPDATE #MyPivotData SET #MyPivotData.LetterDate6 = G3.LetterDate FROM #MyPivotData PD
INNER JOIN (SELECT  AccountNo,LetterDate FROM  #MyDataRows WHERE RowNumber=6) G3
ON PD.AccountNo = G3.AccountNo

SELECT * FROM #MyPivotData

Open in new window


brute force

Author

Commented:
I want to know is there any way to build a query converting columns to row without PIVOT function in sql, For the same task can i have a query withou PIVOt function.
Distinguished Expert 2017

Commented:
You would have to have a third column and then group by AccountNo in your case
The third column would be the counter of letters the specific account has.

Select AccountNo,
        Case when  column3=1 then column2; null,
.
.
         Case when column3=lettern then column2; null
From tablename group by AccountNo
Jeff DarlingDeveloper Analyst

Commented:
The last solution I gave does not use pivot function.  

changed all names containing "PIVOT" to "TEMPOLA"  :/

Create table #MyDataRows (
AccountNo varchar(10),
LetterDate datetime,
rowNumber int
)

INSERT INTO #MyDataRows (AccountNo, LetterDate,rowNumber) 
SELECT *, (
      SELECT COUNT(*)
        FROM #MyData AS counter
      WHERE counter.AccountNo = #MyData.AccountNo
        AND  counter.LetterDate <= #MyData.LetterDate
      ) AS rowNumber
    FROM #MyData
	where accountno='1000888'

Create table #MyTEMPOLAData (
AccountNo varchar(10),
LetterDate1 datetime,
LetterDate2 datetime,
LetterDate3 datetime,
LetterDate4 datetime,
LetterDate5 datetime,
LetterDate6 datetime
)

-- LETTER 1
INSERT INTO #MyTEMPOLAData (AccountNo, LetterDate1)
SELECT AccountNo, LetterDate from (SELECT  AccountNo,LetterDate FROM #MyDataRows WHERE RowNumber=1) G2

-- LETTER 2
UPDATE #MyTEMPOLAData SET #MyTEMPOLAData.LetterDate2 = G3.LetterDate FROM #MyTEMPOLAData PD
INNER JOIN (SELECT  AccountNo,LetterDate FROM  #MyDataRows WHERE RowNumber=2) G3
ON PD.AccountNo = G3.AccountNo

-- LETTER 3
UPDATE #MyTEMPOLAData SET #MyTEMPOLAData.LetterDate3 = G3.LetterDate FROM #MyTEMPOLAData PD
INNER JOIN (SELECT  AccountNo,LetterDate FROM  #MyDataRows WHERE RowNumber=3) G3
ON PD.AccountNo = G3.AccountNo

-- LETTER 4
UPDATE #MyTEMPOLAData SET #MyTEMPOLAData.LetterDate4 = G3.LetterDate FROM #MyTEMPOLAData PD
INNER JOIN (SELECT  AccountNo,LetterDate FROM  #MyDataRows WHERE RowNumber=4) G3
ON PD.AccountNo = G3.AccountNo

-- LETTER 5
UPDATE #MyTEMPOLAData SET #MyTEMPOLAData.LetterDate5 = G3.LetterDate FROM #MyTEMPOLAData PD
INNER JOIN (SELECT  AccountNo,LetterDate FROM  #MyDataRows WHERE RowNumber=5) G3
ON PD.AccountNo = G3.AccountNo

-- LETTER 6
UPDATE #MyTEMPOLAData SET #MyTEMPOLAData.LetterDate6 = G3.LetterDate FROM #MyTEMPOLAData PD
INNER JOIN (SELECT  AccountNo,LetterDate FROM  #MyDataRows WHERE RowNumber=6) G3
ON PD.AccountNo = G3.AccountNo

SELECT * FROM #MyTEMPOLAData

Open in new window

Distinguished Expert 2017

Commented:
Jeff,

Pivot is the straight forward thing as I suggested by reference in the original response.
The manual way the asker has to know the MAX number of columns ahead of time.
and use an aggregation (group by) with case to differentiate among the aggregated data and their column position.

In the example the asker posted, you have 1-7 letters, the asker has almost 21 for the same AccountNo.

The other way I posted, deals with using  COALESCE to combine all the letter dates into a single long column.
The order of the letters might not be in the correct order.....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial