dynamic pivot without aggregate function in sql server
  • Today i am sharing how can we use pivot function without aggregate column data mostly we want to use this kind of non aggregate with datetime column.
    if anyone don't know about pivot so first get idea from this link http://codingresolved.com/discussion/464/convert-rows-into-columns-in-sql-server/p1

    i want arrange datetime column data with pivot

    this is my table script

    CREATE TABLE [dbo].[tblTest](
        [LastName] [varchar](50) NULL,
        [HCStatus] [varchar](50) NULL,
        [Combined] [varchar](50) NULL,
        [Complaint] [varchar](50) NULL,
        [InspectionDate] [datetime] NULL
    ) ON [PRIMARY]


    and table data is like

    image

    so i want to split combined column data with new columns according to unique data contain in combined column and want to show InspectionDate in this.

    so i use this query

    DECLARE @cols AS NVARCHAR(MAX),
     @colsWithNull AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

    select @cols =  stuff(( select '],[' + Combined
    from tblTest group by Combined
    for xml path('')), 1, 2, '') + ']'

    select @colsWithNull =  stuff(( select ', ISnull(Cast([' + Combined +'] as Varchar(20)),'+''''+ 'N/A'+''''+') '+Combined
    from tblTest group by Combined
    for xml path('')), 1, 2, '')

    set @query = 'SELECT LastName,HCStatus, ' + @colsWithNull + ',Complaint from
                 (
      Select LastName,HCStatus,Complaint, Combined,
        InspectionDate,
        cast(InspectionDate as date) date,
        row_number() over(partition by LastName, Combined, cast(InspectionDate as date)
                          order by InspectionDate) rn
      from tblTest
      group by LastName,HCStatus,Complaint, Combined,InspectionDate
    )p
    pivot
    (
      min(InspectionDate)
      For Combined in (' + @cols + ')
    )pvt'

    execute(@query)


    and result for this query is.

     image
    PivotWithOutAggregate1.jpg
    426 x 139 - 36K
    PivotWithOutAggregate2.jpg
    588 x 116 - 33K
  • 9 Comments sorted by
  • Thank you for the article.You are very good.Now what if i want to see only order them by last name?I want to have only one ABC last name?
  • thanks for your comments,
    now if you want to arrange and want to order by it so just add order by in end of query see below bold area.


    DECLARE @cols AS NVARCHAR(MAX),
     @colsWithNull AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

    select @cols =  stuff(( select '],[' + Combined
    from tblTest group by Combined
    for xml path('')), 1, 2, '') + ']'

    select @colsWithNull =  stuff(( select ', ISnull(Cast([' + Combined +'] as Varchar(20)),'+''''+ 'N/A'+''''+') '+Combined
    from tblTest group by Combined
    for xml path('')), 1, 2, '') 

    set @query = 'SELECT LastName,HCStatus, ' + @colsWithNull + ',Complaint from 
                 (
      Select LastName,HCStatus,Complaint, Combined, 
        InspectionDate, 
        cast(InspectionDate as date) date,
        row_number() over(partition by LastName, Combined, cast(InspectionDate as date)
                          order by InspectionDate) rn
      from tblTest
      group by LastName,HCStatus,Complaint, Combined,InspectionDate
    )p
    pivot
    (
      min(InspectionDate)
      For Combined in (' + @cols + ')
    )pvt order by LastName asc'

    execute(@query)

  • I will try and let you know.My goal is to have something like this(see picture)
    Capture.PNG
    1184 x 89 - 91K
  • I got this result with error but i will fix the error(see blank result)
    But i would love to have something like
    LastName HC Status       FALL2013 Compliant HC Status      S2013     Compliant
    Farrell       TB Accredited 26/2/2014  No           TB Accredited 27/2/2014 No
    I attached a sample table so you will understand what i am trying to accomplish.
    So there Farrell(Test) has a lot of previous inspections done with Hc Status,Combined,Compliant and InspectioDate.
    I would love to have one row of inspections result for Farrell and for Testerman etc...

    Thank You
    Capture.PNG
    630 x 83 - 3K
    Capture1.PNG
    528 x 590 - 52K
  • Hi Jaguar may be you get your desire result kindly check my new article of this series
    http://codingresolved.com/discussion/2272/pivot-with-multiple-non-aggregate-columns-sql-server/p1
  • Thank you.You are very bright.
  • Hi Waqas, I have some requirement, which looks simple, but not able to get it. Can you help me out here?

    I have attached a delimited text file, which has the data what I have in a table and what desired output I want.

    Thanks!


    tmpData.txt
    464B
  • Hi @eskay Welcome to forum and thanks for contact but i request ask any question on new discussion and if you consider your question is related to any previous so also put new discussion link on comment.

    and below query will work for you try it .


    DECLARE @cols AS NVARCHAR(MAX),
     @colsWithNull AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)

    select @cols =  stuff(( select '],[' + sProductGroup
    from TableName group by sProductGroup
    for xml path('')), 1, 2, '') + ']'

    set @query = 'SELECT sName,sState, ' + @cols + ' from
                 (
      Select sName,sState,sProductGroup, sValues,
       
        row_number() over(partition by sName, sProductGroup
                          order by sValues) rn
      from TableName
      group by sName,sState,sProductGroup, sValues
    )p
    pivot
    (
      min(sValues)
      For sProductGroup in (' + @cols + ')
    )pvt order by sName asc'

    execute (@query)


  • Awesome! Thank you very much. It worked.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, or you want to Ask a new Question, Please Login or Create a new Account by Clicking below

Login with Facebook

Tagged

Popular Posts of the Week

    Optimum Creative