Solution Required for Mentioned Result.

Question

Post reply

Solution Required for Mentioned Result.

KapilT

SSC Enthusiast

Points: 146
More actions
July 10, 2010 at 3:51 am

#141027

I have two tables with some relational data. find the example below
Columns of Table1
SID
PID
Date
Company
Columns of Table2
SID
PID
Date
ORG
Data for Table1 (providing Data in comma seprated)
100,10,7/6/2009,Airtel
100,10,7/6/2009,BSNL
2000,200,7/17/2009,Voda
2000,200,7/17/2009,Idea
2000,200,7/17/2009,MTNL
30000,3000,7/13/2009,Uninor
30000,3000,7/13/2009,Spice
Data for Table2 (providing Data in comma seprated)
100,10,7/6/2009,Nokia_0
100,10,7/6/2009,Nokia_1
100,10,7/6/2009,Nokia_2
2000,200,7/17/2009,LG_0
2000,200,7/17/2009,LG_1
2000,200,7/17/2009,LG_2
2000,200,7/17/2009,LG_3
2000,200,7/17/2009,LG_4
2000,200,7/17/2009,LG_5
30000,3000,7/13/2009,Sony_1
Expecting Data in below format.
100,10,7/6/2009,Airtel,Nokia_0
100,10,7/6/2009,BSNL,Nokia_1
100,10,7/6/2009,Blank/NULL,Nokia_2
2000,200,7/17/2009,Voda,LG_0
2000,200,7/17/2009,Idea,LG_1
2000,200,7/17/2009,MTNL,LG_2
2000,200,7/17/2009,Blank/NULL,LG_3
2000,200,7/17/2009,Blank/NULL,LG_4
2000,200,7/17/2009,Blank/NULL,LG_5
30000,3000,7/13/2009,Uninor,Sony_1
30000,3000,7/13/2009,Spice,Blank/NULL
in result set Forth and Fifth column having some Blank/Null values thant means cell is Blank.
I am trying to use CET, but i am not sure about that. I have done it for one Group by using temporary table, but it fails for group.
Please find the attached excel sheet, so you will get clear idea.
Please suggest me hint/solution.
Thank You
Jayraj

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

-- See how you start off by actually creating a table

-- and then inserting the data into it? Doing this really

-- makes things a lot easier for all the people you want to

-- help you. So, HELP US HELP YOU by doing this for us! See

-- http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

DECLARE @Table1 TABLE (SID int, PID int, Date datetime, Company varchar(20))

DECLARE @Table2 TABLE (SID int, PID int, Date datetime, ORG varchar(20))

INSERT INTO @Table1

SELECT 100, 10, '7/6/2009', 'Airtel' UNION ALL

SELECT 100, 10, '7/6/2009', 'BSNL' UNION ALL

SELECT 2000, 200, '7/17/2009', 'Voda' UNION ALL

SELECT 2000, 200, '7/17/2009', 'Idea' UNION ALL

SELECT 2000, 200, '7/17/2009', 'MTNL' UNION ALL

SELECT 30000, 3000, '7/13/2009', 'Uninor' UNION ALL

SELECT 30000, 3000, '7/13/2009', 'Spice'

--Data for Table2 (providing Data in comma seprated)

INSERT INTO @Table2

SELECT 100, 10, '7/6/2009', 'Nokia_0' UNION ALL

SELECT 100, 10, '7/6/2009', 'Nokia_1' UNION ALL

SELECT 100, 10, '7/6/2009', 'Nokia_2' UNION ALL

SELECT 2000, 200, '7/17/2009', 'LG_0' UNION ALL

SELECT 2000, 200, '7/17/2009', 'LG_1' UNION ALL

SELECT 2000, 200, '7/17/2009', 'LG_2' UNION ALL

SELECT 2000, 200, '7/17/2009', 'LG_3' UNION ALL

SELECT 2000, 200, '7/17/2009', 'LG_4' UNION ALL

SELECT 2000, 200, '7/17/2009', 'LG_5' UNION ALL

SELECT 30000, 3000, '7/13/2009', 'Sony_1'

-- whew!!! now that the test data has been made,

-- let's see how to do what you're asking for!

;WITH CTE1 AS

(

SELECT *,

RN = row_number() OVER (partition by SID ORDER BY SID,PID,Date)

FROM @Table1

), CTE2 AS

(

SELECT *,

RN = row_number() OVER (partition by SID ORDER BY SID,PID,Date)

FROM @Table2

)

SELECT t2.SID, t2.PID, t2.Date, t1.Company, t2.ORG

FROM CTE2 t2

LEFT JOIN CTE1 t1

ON t1.SID = t2.SID

AND t1.PID = t2.PID

and t1.RN = t2.RN

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

KapilT SSC Enthusiast Points: 146 More actions · Answer 2

Dear WayneS,

Thanks for providing Solution and suggestions. i will take care of ur suggesions.

I have made some changes as below to get exact result set.

thank you again.

SELECT DISTINCT A.SID, A.PID, A.Date, ISNULL (A.Company,'') As Company, ISNULL(A.ORG,'') AS Org,A.RN FROM (

SELECT t2.SID, t2.PID, t2.Date, t1.Company, t2.ORG,t2.RN

FROM CTE2 t2

Left JOIN CTE1 t1

ON t1.SID = t2.SID

AND t1.PID = t2.PID

and t1.RN = t2.RN

UNION ALL

SELECT t1.SID, t1.PID, t1.Date, t1.Company, t2.ORG,t1.RN

FROM CTE2 t2

Right JOIN CTE1 t1

ON t2.SID = t1.SID

AND t2.PID = t1.PID

and t2.RN = t1.RN

)A

Order by

A.SID, A.PID, A.Date,A.RN

Thanks

Jayraj

SAVE WATER - SAVE LIFE

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

You can also get your desired result by replacing the LEFT JOIN IN Wayne's Code with a FULL OUTER JOIN

SELECTISNULL( t2.SID, t1.SID ) SID, ISNULL( t2.PID, t1.PID ) PID,

ISNULL( t2.Date, t1.Date ) Date, t1.Company, t2.ORG

FROM CTE2 t2

FULL OUTER JOIN CTE1 t1

ON t1.SID = t2.SID

AND t1.PID = t2.PID

and t1.RN = t2.RN

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

KapilT SSC Enthusiast Points: 146 More actions · Answer 4

Dear Kingston,

Thanks, its helpful.

Regards,

Jayraj