Want to bring data in specific format(Details given in message body)

Question

Want to bring data in specific format(Details given in message body)

avdhut.k

SSCrazy

Points: 2834
More actions
December 20, 2013 at 5:31 am

#302569

Hi ,
I had some data state,region and level wise which stored in table --> #temp_f
I need Output given in table -- > #Temp_Output
Create Table #temp_f
(
state varchar(50),
region varchar(100),
username varchar(100),
level Int
)
Insert Into #temp_f
values ('maharashtra','nagpur','john',1),
('maharashtra','nagpur','villy',1),
('maharashtra','nagpur','michael',2),
('maharashtra','mumbai','ABC',1),
('maharashtra','mumbai','XYZ',2),
('maharashtra','mumbai','kezy',2),
('maharashtra','mumbai','lace',2),
('Delhi','raipur','jake',1),
('Delhi','raipur','make',2),
('Delhi','mantar','taky',1)
--Select * from #temp_f
create table #Temp_Output
(
state varchar(50),
region varchar(100),
username_1 varchar(100),
username_2 varchar(100)
)
Insert Into #Temp_Output
values ('maharashtra','nagpur','john','michael'),
('maharashtra','nagpur','villy',''),
('maharashtra','mumbai','ABC','XYZ'),
('maharashtra','mumbai','','kezy'),
('maharashtra','mumbai','','lace'),
('Delhi','raipur','jake','make'),
('Delhi','raipur','taky','')
Select * from #temp_f
Select * from #Temp_Output
Please help me as I need this urgently.
Thanks in Advance!

Viewing 7 posts - 1 through 6 (of 6 total)

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

higgim SSCarpal Tunnel Points: 4090 More actions · Answer 1

This is by far not the best bit of coding as I broke to problem down into stages to build the final result. I did have to change your test data to the below as the expected output did not match the test data (('Delhi','mantar','taky',1) changed to Raipur. This will ONLY cater for 2 levels of user at present.

So with your test data the below seems to work and the results appear to match. Note that Grouper needs to be in there. In theory you could get rid of it later on.

CREATE TABLE #temp_f

(

state VARCHAR(50) ,

region VARCHAR(100) ,

username VARCHAR(100) ,

level INT

)

INSERT INTO #temp_f

VALUES ( 'maharashtra', 'nagpur', 'john', 1 ),

( 'maharashtra', 'nagpur', 'villy', 1 ),

( 'maharashtra', 'nagpur', 'michael', 2 ),

( 'maharashtra', 'mumbai', 'ABC', 1 ),

( 'maharashtra', 'mumbai', 'XYZ', 2 ),

( 'maharashtra', 'mumbai', 'kezy', 2 ),

( 'maharashtra', 'mumbai', 'lace', 2 ),

( 'Delhi', 'raipur', 'jake', 1 ),

( 'Delhi', 'raipur', 'make', 2 ),

( 'Delhi', 'raipur', 'taky', 1 );

WITH partitionedusers

AS ( SELECT state ,

region ,

username ,

level ,

ROW_NUMBER() OVER ( PARTITION BY STATE, region, level ORDER BY state, region, level ) AS Grouper

FROM #temp_f

),

level1

AS ( SELECT state ,

region ,

username ,

Grouper

FROM partitionedusers

WHERE level = 1

),

level2

AS ( SELECT state ,

region ,

username ,

Grouper

FROM partitionedusers

WHERE level = 2

),

distinctregions

AS ( SELECT DISTINCT

state ,

region ,

Grouper

FROM partitionedusers

)

SELECT dr.state ,

dr.region ,

dr.Grouper ,

ISNULL(L1.username, '') AS username_1 ,

ISNULL(L2.username, '') AS uername_2

FROM distinctregions dr

LEFT OUTER JOIN level1 L1 ON dr.region = L1.region

AND dr.state = L1.state

AND dr.Grouper = l1.Grouper

LEFT OUTER JOIN level2 L2 ON dr.region = L2.region

AND dr.state = L2.state

AND dr.Grouper = l2.Grouper

DROP TABLE #temp_f

CREATE TABLE #Temp_Output

(

state VARCHAR(50) ,

region VARCHAR(100) ,

username_1 VARCHAR(100) ,

username_2 VARCHAR(100)

)

INSERT INTO #Temp_Output

VALUES ( 'maharashtra', 'nagpur', 'john', 'michael' ),

( 'maharashtra', 'nagpur', 'villy', '' ),

( 'maharashtra', 'mumbai', 'ABC', 'XYZ' ),

( 'maharashtra', 'mumbai', '', 'kezy' ),

( 'maharashtra', 'mumbai', '', 'lace' ),

( 'Delhi', 'raipur', 'jake', 'make' ),

( 'Delhi', 'raipur', 'taky', '' )

SELECT *

FROM #Temp_Output

DROP TABLE #Temp_Output

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

;WITH SequencedData AS (

SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY state, region, level ORDER BY (SELECT NULL)) FROM #temp_f)

SELECT

state,

region,

username_1 = MAX(CASE WHEN level = 1 THEN username ELSE '' END),

username_2 = MAX(CASE WHEN level = 2 THEN username ELSE '' END)

FROM SequencedData

GROUP BY state, region, rn

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

avdhut.k SSCrazy Points: 2834 More actions · Answer 3

avdhut.k

SSCrazy

Points: 2834

December 20, 2013 at 10:39 pm

#1675365

Thanks cris!!!!

Its working as per my requirement.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

avdhut.k (12/20/2013)
Thanks cris!!!!
Its working as per my requirement.

So.... wacha wanna do when level 3 shows up?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

avdhut.k (12/20/2013)
Thanks cris!!!!
Its working as per my requirement.

You're welcome - but do think about Jeff's point. In case you're wondering, there is a solution for an unknown number of levels. It's called a dynamic crosstab and Jeff's written an excellent article about it for ssc: http://www.sqlservercentral.com/articles/Crosstab/65048/[/url].

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

avdhut.k SSCrazy Points: 2834 More actions · Answer 6

hi Cris and Jeff,

Actualy we had requirement upto 3 levels only.

So I done changes accordingly.

So its working for me.

Thanks Cris and jeff a lot!!!!