Forum guidelines
AuthorQuestion
how to switch rows of table of sql to column of gridview
Posted By Rushal Arora on 21 Aug 2013




how to switch rows of a table of sql server to column of gridview ?


AuthorReply
Re: how to switch rows of table of sql to column of gridview
Posted By Iftikar Hussain on 21 Aug 2013  
Hi,
        Create a procedure in your sql for the below query and then call your stored procedure from your code

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

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                    from yourtable
                    group by ColumnName, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ' + @cols + ' from 
             (
                select value, ColumnName
                from yourtable
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + ')
            ) p '

execute(@query)

Regards,
Iftikar

Remember to click "Mark as Answer" on the post, if it helps you.
Join a Chapter

SPONSORED BY

Spire.Doc - Free .NET Word API
Use Spire.Doc to create, read, write, print, and convert word documents to OpenXML, RTF, TXT, XPS, EPUB, EMF, HTML, Image and PDF.