October 12, 2009 at 10:03 am
Hi I have a data set that I want to present in a certain format. I have attached a spreadsheet with the sample data as well as what it should look like. Whats the best approach
It should look like this
Loanref Notice1 Notice2 Notice3
I1761162 INTENTION TO SURRENDER NOTARIAL BOND FINAL SEQUESTRATION
I1748637 NOTARIAL BONDS CANCELLED FINAL SEQUESTRATION REHABILITATION
QV420079 REHABILITATION FINAL SEQUESTRATION PROVISIONAL SEQUESTRATION
I1756079 NOTARIAL BONDS CANCELLED ADMIN ORDER RESCINDED DUE TO NOT PAID UP ADMINISTRATION ORDER
Ranga
October 12, 2009 at 11:41 am
If you run your results to text, does the following get you what you're after?
SET NOCOUNT ON
SELECT
'Loanref Notice1 Notice2 Notice3'
SELECT
[Loanref] + ' ' + [Notice1] + ' ' + [Notice2] + ' ' + [Notice3]
FROM #test
October 12, 2009 at 11:46 am
Unfortunately, you didn't describe the rules for building the groups. 🙁
First I thought you're looking for min(RowId) per Loanref, but that's obviously not true, because rowid 11 and 12 wouldn't match that pattern.
You might want to have a look at ROW_NUMBER function in BooksOnLine (SQL Server help system, usually installed together with SQL Server).
If you need further help please post the code you've tried and what you're struggling with....
October 12, 2009 at 12:19 pm
The rules for this is that loanref is distinct and should look like the result set in the excel spreadsheet
Ranga
October 12, 2009 at 12:28 pm
Oh, I'm sorry!
I didn't realize that Notice1, Notice2,and Notice3 are identical in your sample data... My fault. You most probably have valid reasons for adding those columns in your sample data without specifically telling that the data are redundant for each row. They are, aren't they??
In that case I'd like to point you to the second and third article in the "links for" list in my signature. If you always need to return three (or any other predetermined number) columns then you should read the second article, otherwise the third. Or better: read both to see the difference. 😉
October 12, 2009 at 12:32 pm
Here's what I can come up with given what you've supplied so far. Will this do? What do you do if there are more than 3 notices with one loan?
create table #Notices (
ID int identity
primary key,
LoanRef varchar(20),
Notice varchar(100)) ;
insert into
#Notices (LoanRef, Notice)
select
'I1761162',
'INTENTION TO SURRENDER'
union all
select
'I1761162',
'NOTARIAL BOND'
union all
select
'I1761162',
'FINAL SEQUESTRATION'
union all
select
'I1748637',
'NOTARIAL BONDS CANCELLED'
union all
select
'I1748637',
'FINAL SEQUESTRATION'
union all
select
'I1748637',
'REHABILITATION' ;
;
with
Notices(LoanRef, Notice, Row)
as (select
LoanRef,
Notice,
row_number() over (partition by LoanRef order by ID)
from
#Notices)
select
N1.LoanRef,
N1.Notice as Notice1,
N2.Notice as Notice2,
N3.Notice as Notice3
from
Notices N1
left outer join Notices N2
on N1.LoanRef = N2.LoanRef
and N1.Row = 1
and N2.Row = 2
left outer join Notices N3
on N1.LoanRef = N3.LoanRef
and N1.Row = 1
and N3.Row = 3
where
N1.Row = 1
order by
N1.LoanRef ;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 12, 2009 at 12:46 pm
Hi Gus,
to me it looks like a "standard row to col problem"...
;with
cte AS (
SELECT
row_number () OVER (partition BY loanref ORDER BY rowid) AS row,
rowid,
loanref, notice1
FROM #test
)
SELECT loanref,
MAX(CASE WHEN row=1 THEN notice1 ELSE '' END) AS Notice1,
MAX(CASE WHEN row=2 THEN notice1 ELSE '' END) AS Notice2,
MAX(CASE WHEN row=3 THEN notice1 ELSE '' END) AS Notice3
FROM cte
GROUP BY loanref
ORDER BY MIN(rowid)
October 12, 2009 at 2:34 pm
Thank you guys... I now understand....
Ranga
October 12, 2009 at 3:27 pm
I want to move this code to informix as well. Can CTEs work when you use sql fro informix ?
-- Create test Table
IF OBJECT_ID('tempdb..#Test') IS NOT NULL
DROP TABLE #Test
GO
CREATE TABLE #Test
(
RowId INT IDENTITY(1,1)
,Loanref VARCHAR(30)
,Notice1 VARCHAR(100)
,Notice2 VARCHAR(100)
,Notice3 VARCHAR(100)
)
---Input data
INSERT #Test VALUES('I1761162','INTENTION TO SURRENDER','INTENTION TO SURRENDER','INTENTION TO SURRENDER')
INSERT #Test VALUES('I1761162','NOTARIAL BOND','NOTARIAL BOND','NOTARIAL BOND')
INSERT #Test VALUES('I1761162','FINAL SEQUESTRATION','FINAL SEQUESTRATION','FINAL SEQUESTRATION')
INSERT #Test VALUES('I1748637','NOTARIAL BONDS CANCELLED','NOTARIAL BONDS CANCELLED','NOTARIAL BONDS CANCELLED')
INSERT #Test VALUES('I1748637','FINAL SEQUESTRATION','FINAL SEQUESTRATION','FINAL SEQUESTRATION')
INSERT #Test VALUES('I1748637','REHABILITATION','REHABILITATION','REHABILITATION')
INSERT #Test VALUES('QV420079','REHABILITATION','REHABILITATION','REHABILITATION')
INSERT #Test VALUES('QV420079','FINAL SEQUESTRATION','FINAL SEQUESTRATION','FINAL SEQUESTRATION')
INSERT #Test VALUES('QV420079','PROVISIONAL SEQUESTRATION','PROVISIONAL SEQUESTRATION','PROVISIONAL SEQUESTRATION')
INSERT #Test VALUES('I1756079','NOTARIAL BONDS CANCELLED','NOTARIAL BONDS CANCELLED','NOTARIAL BONDS CANCELLED')
INSERT #Test VALUES('I1756079','ADMIN ORDER RESCINDED DUE TO NOT PAID UP','ADMIN ORDER RESCINDED DUE TO NOT PAID UP','ADMIN ORDER RESCINDED DUE TO NOT PAID UP')
INSERT #Test VALUES('I1756079','ADMINISTRATION ORDER','ADMINISTRATION ORDER','ADMINISTRATION ORDER'
)
-- Select statement to display input data
SELECT * FROM #Test
--- SOLUTION 1:- CTE to Format the Data
; WITH
CTE AS
(
SELECT
Row_number () OVER (PARTITION BY Loanref ORDER BY Rowid) AS Row
,Rowid
,Loanref
,Notice1
FROM
#Test
)
SELECT Loanref,
MAX(CASE WHEN Row=1 THEN Notice1 ELSE '' END) AS Notice1,
MAX(CASE WHEN Row=2 THEN Notice1 ELSE '' END) AS Notice2,
MAX(CASE WHEN Row=3 THEN Notice1 ELSE '' END) AS Notice3
FROM CTE
GROUP BY Loanref
ORDER BY MIN(Rowid
)
Ranga
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply