Post reply

Very Complicate Query....

  • February 12, 2011 at 7:41 am

    #234352

    Hi, this is my table with records

    CREATE TABLE [dbo].[Person](

    [RtnSetId] [int] NOT NULL,

    [FirstName] [nvarchar](50) NULL,

    [SSN] [nvarchar](50) NULL,

    [PersonRole] [nchar](10) NULL

    ) ON [PRIMARY]

    GO

    INSERT [dbo].[Person] ([RtnSetId], [FirstName], [SSN], [PersonRole]) VALUES (1, N'A', N'12345', N'P ')

    INSERT [dbo].[Person] ([RtnSetId], [FirstName], [SSN], [PersonRole]) VALUES (1, N'A', N'12345', N'S ')

    INSERT [dbo].[Person] ([RtnSetId], [FirstName], [SSN], [PersonRole]) VALUES (1, N'A', N'12345', N'D ')

    INSERT [dbo].[Person] ([RtnSetId], [FirstName], [SSN], [PersonRole]) VALUES (1, N'A', N'12345', N'D ')

    INSERT [dbo].[Person] ([RtnSetId], [FirstName], [SSN], [PersonRole]) VALUES (1, N'A', N'12345', N'D ')

    INSERT [dbo].[Person] ([RtnSetId], [FirstName], [SSN], [PersonRole]) VALUES (2, N'B', N'67890', N'P ')

    INSERT [dbo].[Person] ([RtnSetId], [FirstName], [SSN], [PersonRole]) VALUES (2, N'B', N'67890', N'S ')

    I want the output as :

    1) It should generate dynamic columns

    2) It should pick personrole in the order of 'P', 'S', 'D'

    3) Record should be updated into column wise based on the group by rtnsetid

    RtnSetId1FirstName1SSN1PersonRole1RtnSetId2FirstName2SSN2PersonRole2RtnSetId3FirstName3SSN3PersonRole3RtnSetId4FirstName4SSN4PersonRole4RtnSetId5FirstName5SSN5PersonRole5

    1A12345P 1A12345S 1A12345D 1A12345D 1A12345D

    2B67890P 2B67890S

    Pls. anybody who really interested in SQL's help me out.

    Thanks

  • February 12, 2011 at 9:42 am

    #1286029

    That sounds like a job forCrossTab together with Row_Number to get it sorted as requested.

    If you can't predict the max. number of column groups (e.g. the 5 you posted) you might want to change it into dynamic SQL.

    Here's a sample of the static CrossTab method, limited to the first two RtnSetId's:

    WITH cte AS

    (

    SELECT

    *,

    ROW_NUMBER() OVER(PARTITION BY rtnsetid ORDER BY

    CASE

    WHEN [PersonRole]='P' THEN 1

    WHEN [PersonRole]='S' THEN 2

    WHEN [PersonRole]='D' THEN 3

    ELSE 4 END ) AS pos

    FROM person

    )

    SELECT

    MAX(CASE WHEN pos=1 THEN RtnSetId ELSE NULL END) AS RtnSetId1,

    MAX(CASE WHEN pos=1 THEN FirstName ELSE NULL END) AS FirstName1,

    MAX(CASE WHEN pos=1 THEN SSN ELSE NULL END) AS SSN1,

    MAX(CASE WHEN pos=1 THEN PersonRole ELSE NULL END) AS PersonRole1,

    MAX(CASE WHEN pos=2 THEN RtnSetId ELSE NULL END) AS RtnSetId2,

    MAX(CASE WHEN pos=2 THEN FirstName ELSE NULL END) AS FirstName2,

    MAX(CASE WHEN pos=2 THEN SSN ELSE NULL END) AS SSN2,

    MAX(CASE WHEN pos=2 THEN PersonRole ELSE NULL END) AS PersonRole2

    FROM cte

    GROUP BY RtnSetId


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 12, 2011 at 10:04 am

    #1286032

    What a genius you are....amazing ....thanks a lot friend.

  • February 12, 2011 at 4:09 pm

    #1286079

    Glad I could help 😀


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 12, 2011 at 5:51 pm

    #1286087

    LutzM (2/12/2011)

    Glad I could help 😀

    It's not my job to judge but I've got to say it anyway... nice job, Lutz! 🙂

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

  • February 12, 2011 at 6:03 pm

    #1286090

    Please may I ask - what is the purpose of this request ?

    Also, how is this going to help if there are more than a few records?

    e.g. Lutz' fine answer is currently limited (but expandable manually - or - as stated - by dynamic sql) to handling two rows of input per RtnSetId

    I can't help thinking that this may be a case of trying to do a presentation job in T-SQL that might be more suited to being handled elsewhere...

    MM


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
    • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
    • [/url]
  • How to Post Performance Problems - by Gail Shaw
    • [/url]

  • February 12, 2011 at 8:05 pm

    #1286104

    Lutz,

    Is it possible to make the pos=1 and RtnSetid1 dynamic as

    By declaring variable value upto 6 for ex. x <= 6 posx and RtnSetidx likethat....

    Kindly help me on this, bcoz am new to sql.

    Thanks.

  • February 12, 2011 at 10:09 pm

    #1286108

    rajesh-303966 (2/12/2011)

    Lutz,

    Is it possible to make the pos=1 and RtnSetid1 dynamic as

    By declaring variable value upto 6 for ex. x <= 6 posx and RtnSetidx likethat....

    Kindly help me on this, bcoz am new to sql.

    Thanks.

    That's precisely why Mr Magoo was asking the question. That's something that should be handled at the presentation layer. Not by t-sql.

    While it CAN be done. It's rarely the best solution.

    I personnally never had to resort to this solution, ever.

  • February 13, 2011 at 3:24 am

    #1286124

    Ninja's_RGR'us (2/12/2011)

    ...

    That's precisely why Mr Magoo was asking the question. That's something that should be handled at the presentation layer. Not by t-sql.

    While it CAN be done. It's rarely the best solution.

    I personnally never had to resort to this solution, ever.

    I use this method once in a while to format data that will be emailed as an Excel attachement.

    Instead of stressing SSIS for that simple task, I rather use a few lines of T-SQL.

    Therefore, I forgot to mention the presentation layer preference. But I agree: if there is one, this kind of formatting should be done there.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 13, 2011 at 3:39 am

    #1286126

    rajesh-303966 (2/12/2011)

    Lutz,

    Is it possible to make the pos=1 and RtnSetid1 dynamic as

    By declaring variable value upto 6 for ex. x <= 6 posx and RtnSetidx likethat....

    Kindly help me on this, bcoz am new to sql.

    Thanks.

    You could expand the code I provided down to

    MAX(CASE WHEN pos=6 THEN PersonRole ELSE NULL END) AS PersonRole6

    by simply copy & paste plus minor editing of the pos value and the column name.

    This solution will always display 6 groups of columns even if the last x columns will be empty for each and every row.

    If you need a variable number of columns based on the max. value of pos then you'd need to use dynamic SQL. I recommend you read the DynamicCrossTab article referenced in my signature for more details. Give it a try and see if you can convert the static solution into a dynamic version.

    The reason I'm not providing the dynamic solution: this concept is slightly beyond a beginners level. In order to be able to support this solution you'll need to understand how it works instead of just using a solution copied off the web... 😉


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • February 13, 2011 at 9:14 pm

    #1286246

    @Lutz - very nice. I don't think that I've ever used a case statement on the order by of one of the windowing functions... nice to see it!

    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

    • Viewing 11 posts - 1 through 10 (of 10 total)

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