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

Question

Post reply

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: 1000539 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)
Intro to Tally Tables and Functions

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://qa.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

avdhut.k

SSCrazy

Points: 2834

December 23, 2013 at 2:08 am

#1675522

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!!!!