Post reply

Converting rows into columns

  • February 17, 2010 at 4:50 am

    #219219

    Hi,

    My table containing below data.

    Name Date

    A 1/1/07

    B 12/2/07

    C 1/1/08

    I want my query results are as follows:

    Name Date 1/1/07 12/2/07 1/1/08

    A 1/1/07 1 0 0

    B 12/2/07 0 1 0

    C 1/1/08 0 0 1

    Is it possible to retrieve the query results like above? If yes please help me by posting the query. Kindly please help very much urgent. Please note the data is not static it is dynamic.

    Thanks in advance.

    Thanks,

    Madhuri

  • February 17, 2010 at 6:54 am

    #1120045

    Hi

    Not any much of help... but the below link, is on the same wave.

    http://qa.sqlservercentral.com/Forums/Topic866779-392-1.aspx

    and in between

    the example was given to me by "Dave Ballantyne" might help you... its good.

    http://www.sqlprof.com/blogs/sqldev/archive/2008/04/12/pivots-with-dynamic-columns-in-sql-server-2005-2008.aspx

    -r WW -n RMudugal

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • February 17, 2010 at 7:33 am

    #1120081

    You might find what you're looking for in these articles:

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns [/url]

    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

    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

  • February 17, 2010 at 8:30 am

    #1120144

    Try this...

    create table #pvt (name varchar(100), Date datetime) alter table #pvt alter column date datetime

    insert into #pvt (Name, Date) values ('A','1/1/07')

    insert into #pvt (Name, Date) values ('B','12/2/07')

    insert into #pvt (Name, Date) values ('C','1/1/08')

    select * from #pvt

    select name, [2007-01-01 00:00:00.000] as '1/1/07' ,[2007-12-02 00:00:00.000] as '12/2/07', [2008-01-01 00:00:00.000] as '1/1/08'

    from (

    select name, Date from #pvt) up

    pivot (count(Date) for [Date] IN ([2007-01-01 00:00:00.000],[2007-12-02 00:00:00.000], [2008-01-01 00:00:00.000])) as pvt

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

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