Post reply

Question on PIVOT

  • February 4, 2015 at 1:53 pm

    #295180

    I have output like this

    Name Type Value

    Sarath Age 33

    Sarath Id 333

    Mahesh Age 22

    mahesh Id 222

    I'm finding it difficult to transpose after grouping.

    I need something like this.

    Name Age Id

    Sarath 33 333

    Mahesh 22 222

    To add this, I don't want to hard code as FOR Type IN [Age], [ID]

    I'm looking for something that I can make FOR Type IN (DYNAMIC COLUMNS)

    Can anyone please provide me a hint on how to do it? Thanks

  • February 4, 2015 at 2:18 pm

    #1775470

    You will need a dynamic cross tab or pivot to get what you want, which means using dynamic SQL

    An article on how[/url]

  • February 4, 2015 at 2:30 pm

    #1775473

    Thanks for the reply. Yes, you are right. I did using dynamic @sql by creating columns on the fly. Do you think it is the only way?

  • February 4, 2015 at 2:37 pm

    #1775476

    Yes

  • February 4, 2015 at 2:42 pm

    #1775478

    I have one add on question. Not sure if it needs to be a different topic.

    Assume I have data like

    Name Type Value

    Sarath Age 33

    Sarath Id 333

    Sarath Id 123

    Mahesh Age 22

    mahesh Id 222

    Would I be able to get output like?

    Name Age Id

    Sarath 33 333, 123

    Mahesh 22 222

    I just want to concatenate somehow if there are multiple values matching up with a column. Thanks

  • February 4, 2015 at 3:02 pm

    #1775487

    Should I assume it still has to be dynamic? In other words, that could happen for any Type value and each should be handled the same way?

  • February 4, 2015 at 3:31 pm

    #1775496

    We're kind of nesting a couple of common questions (dynamic pivots and concatenation) here.

    Basically you do the concatenation first using STUFF and FOR XML PATH to get the concatenated value for each field for each person. Do that in a CTE in your dynamic SQL and query it instead of the table.

    CREATE TABLE #PivotProblem

    (PersonName varchar(10),

    Fieldname varchar(10),

    Fieldvalue int)

    INSERT #PivotProblem (PersonName, Fieldname, Fieldvalue)

    SELECT 'Sarath' PersonName, 'Age' Fieldname, 33 Fieldvalue

    UNION ALL

    SELECT 'Sarath', 'Id', 333

    UNION ALL

    SELECT 'Sarath', 'Id', 123

    UNION ALL

    SELECT 'Mahesh', 'Age', 22

    UNION ALL

    SELECT 'mahesh', 'Id', 222

    DECLARE @MyQuery NVARCHAR(MAX)

    DECLARE @FieldsList VARCHAR(MAX)

    SELECT @FieldsList = STUFF(( SELECT ',' + Fieldname

    FROM #PivotProblem

    GROUP BY Fieldname

    ORDER BY Fieldname

    FOR XML PATH ('')),1,1,'')

    SET @MyQuery = N';WITH PivotConcat AS (

    SELECT PersonName, FieldName, STUFF (( SELECT '','' + CAST (p2.Fieldvalue AS VARCHAR)

    FROM #PivotProblem p2

    WHERE p2.PersonName = p1.PersonName

    AND p2.Fieldname = p1.FieldName

    FOR XML PATH ('''')

    ),1,1,'''') FieldValues

    FROM #PivotProblem P1

    GROUP BY PersonName, FieldName

    )

    SELECT PersonName,' + '['+Replace(@FieldsList,',','],[') + ']' + ' FROM PivotConcat PIVOT (MIN(Fieldvalues) FOR Fieldname

    IN (['+Replace(@FieldsList,',','],[') + '])) p '

    PRINT @MyQuery

    EXEC sp_executesql @MyQuery

    DROP TABLE #PivotProblem

    PS> Now I need an aspirin.

  • February 5, 2015 at 7:48 am

    #1775693

    Thanks a lot!! I didn't expect you giving me complete code 🙂

    PS: Hope you took Aspirin!

  • February 5, 2015 at 10:36 am

    #1775789

    sarath.tata (2/5/2015)

    Thanks a lot!! I didn't expect you giving me complete code 🙂

    PS: Hope you took Aspirin!

    With something like that sometimes its easier to provide in working code than just explain in english.

Viewing 9 posts - 1 through 8 (of 8 total)

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