Post reply

Query help

  • March 25, 2013 at 7:09 pm

    #270374

    I need a query to get the expected output

    Table:Student

    stguid stuName studwor stid

    ------------------------ ----------------- ----------------- -----------

    642-4d5d-9af0-4c7a18ddChrisNameChris 255

    4171-8655-2de255b88e08ChrisCity SAN City 179

    2a0d-4100-bd1c-343882ChrisCounty Wendy 179

    48f0-b455-5207b187e639ChrisphoneNumberThis is a test phone 179

    4d5d-9af0-4c7a18ddd7b2ChrisDNameWTS Test 180

    4041-ba50-1085acf7d86cChrisDTypeThis is for Dtpetest 180

    Expected output:

    ChrisNameChrisCity ChrisCounty ChrisphoneNumberChrisphoneNumberChrisDType

    ---------- ------------ ------------- ----------------- ---------------- ----------

    Chris SAN City WendyThis is a test phone WTS Test This is for Dtpetest

    Thanks for help in advance.

  • March 26, 2013 at 2:43 am

    #1600653

    What is your criteria for the expected results?

    The Name?

    Id?

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • March 26, 2013 at 3:05 am

    #1600663

    Hi,

    This should get the required output, but if the stuName values change and there's more records in there then it won't work. In which case I can only think that you'd need to do something using dynamic SQL. Can you change the design of the table?

    select

    max(ChrisName) ChrisName

    ,max(ChrisCity) ChrisCity

    ,max(ChrisCounty) ChrisCounty

    ,max(ChrisphoneNumber) ChrisphoneNumber

    ,max(ChrisDName) ChrisDName

    ,max(ChrisDType) ChrisDType

    from

    (select * from student) as src

    pivot

    (

    max(studwor)

    for stuName in (

    ChrisName

    ,ChrisCity

    ,ChrisCounty

    ,ChrisphoneNumber

    ,ChrisDName

    ,ChrisDType

    )

    ) as pvt

  • March 26, 2013 at 9:31 pm

    #1601015

    I have provided sample data but i have 100 colmnns

  • March 27, 2013 at 4:11 am

    #1601065

    do you mean 100 columns or 100 rows? Assuming that you mean rows then how do you know those particular 6 out of the 100 belong together? Can you post a few more 'sets' so that we can see what you mean? Or if you do mean columns can you post some more of those?

  • March 27, 2013 at 5:37 am

    #1601091

    nonstandard ...

          In context .. the modeling is correct (or the default, at least I think so, sorry...;-)):

    create table #students

    (id int identity (1,1) not null,

    Name varchar(200),

    City varchar(100),

    County varchar(100),

    PhoneNumber varchar(100),

    Dname varchar(100),

    DType varchar(100))

         this way, you get the desired results more easily ...

    gives the impression that you're thinking in Excel (for its modeling presented) ...

  • March 27, 2013 at 9:03 am

    #1601204

    I am working on reports but it is denormalized database..

  • March 27, 2013 at 9:05 am

    #1601205

    for id there would be stuname like 100 row value which should be display as columns..

  • April 3, 2013 at 3:42 am

    #1603073

    Mvs2k11 (3/27/2013)

    for id there would be stuname like 100 row value which should be display as columns..

    If you have 100 rows to pivot and are not sure about addition or deletion of rows(which need to be pivoted) in the future, then you should use a Dynamic Cross Tab. Its very very handy when it comes to Reporting.

    Here is how you do it on your data:

    --DDL and SampleData

    Create Table Ex

    (

    stguid Varchar(100),

    stuNameVarchar(100),

    studwor Varchar(100),

    stid Int

    )

    Insert Into Ex

    Select '642-4d5d-9af0-4c7a18dd','ChrisName','Chris',255

    Union ALL

    Select '4171-8655-2de255b88e08','ChrisCity', 'SAN City',179

    Union ALL

    Select '2a0d-4100-bd1c-343882','ChrisCounty', 'Wendy',179

    Union ALL

    Select '48f0-b455-5207b187e639','ChrisphoneNumber','This is a test phone',179

    Union ALL

    Select '4d5d-9af0-4c7a18ddd7b2','ChrisDName','WTS Test',180

    Union ALL

    Select '4041-ba50-1085acf7d86c','ChrisDType','This is for Dtpetest',180

    --Dynamic Cross Tab

    Declare @sql Varchar(MAX)

    Select @sql = 'Select ' + STUFF((Select DISTINCT ',MAX(Case When stuName = ' + CHAR(39) + stuName + CHAR(39) + ' Then studwor Else '''' End) As ' + stuName From Ex FOR XML PATH('')),1,1,'')

    Select @sql = @sql + ' From

    (

    Select *, ROW_NUMBER() Over(PARTITION By stguid Order By stguid) As rn From Ex

    ) As a

    Group By rn '

    Execute(@sql)

    I added the Row_Number() just so that I could Group the data. This would work in case of the uncertainty you have in the no. of columns to be pivoted.

    Hope this helps 🙂

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • April 6, 2013 at 9:19 am

    #1604225

    Thanks a lot it works...

  • April 7, 2013 at 10:58 pm

    #1604279

    Mvs2k11 (4/6/2013)

    Thanks a lot it works...

    You're Welcome. 🙂

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

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

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