SQL that will turn the rows in to columnsin date format

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.
chandan mAsked:
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.

arnoldCommented:
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...
0
chandan mAuthor 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.
0
Shalu MCommented:
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

0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

arnoldCommented:
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
0
arnoldCommented:
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.
0
Jeff DarlingDeveloper AnalystCommented:
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
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
chandan mAuthor 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....
0
arnoldCommented:
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
0
chandan mAuthor 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.
0
chandan mAuthor 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.
0
Shalu MCommented:
Did you try using the query I posted?
0
arnoldCommented:
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)
0
Jeff DarlingDeveloper AnalystCommented:
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
0
chandan mAuthor Commented:
Query works fine but can you explain me the query functions,
0
Jeff DarlingDeveloper AnalystCommented:
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
0
chandan mAuthor Commented:
Best solution helped me a lot
0
chandan mAuthor Commented:
can you send me a link where i can learn more about pivot and understand more on it
0
chandan mAuthor Commented:
is there any other way other than pivot function, can we display it without pivot function
0
arnoldCommented:
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
0
chandan mAuthor Commented:
How to get a single account no and display it in column with same query,
0
chandan mAuthor 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
0
chandan mAuthor Commented:
i need to check for single account # to display the letter 1, letter 2 and so on in column
0
chandan mAuthor Commented:
can i have a query for the same output without pivot function in sql
0
Jeff DarlingDeveloper AnalystCommented:
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
0
chandan mAuthor 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.
0
arnoldCommented:
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
0
Jeff DarlingDeveloper AnalystCommented:
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

0
arnoldCommented:
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.....
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
SQL

From novice to tech pro — start learning today.