Avatar of chandan m
chandan m
 asked on

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.
SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
arnold

8/22/2022 - Mon
arnold

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...
chandan m

ASKER
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.
Shalu M

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
arnold

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
arnold

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.
ASKER CERTIFIED SOLUTION
Jeff Darling

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
chandan m

ASKER
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....
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

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
chandan m

ASKER
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.
chandan m

ASKER
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Shalu M

Did you try using the query I posted?
arnold

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 Darling

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
chandan m

ASKER
Query works fine but can you explain me the query functions,
Jeff Darling

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
chandan m

ASKER
Best solution helped me a lot
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
chandan m

ASKER
can you send me a link where i can learn more about pivot and understand more on it
chandan m

ASKER
is there any other way other than pivot function, can we display it without pivot function
arnold

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
chandan m

ASKER
How to get a single account no and display it in column with same query,
chandan m

ASKER
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
chandan m

ASKER
i need to check for single account # to display the letter 1, letter 2 and so on in column
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
chandan m

ASKER
can i have a query for the same output without pivot function in sql
Jeff Darling

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
chandan m

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
arnold

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 Darling

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

arnold

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.....
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes