How convert rows to columns in SQL Server

Question

I'm looking for an efficient way to convert rows to columns with dynamic column names in SQL Server. I heard that PIVOT is not very fast, and I need to deal with a lot of records.

This is my example:

Id  Name      Type         Address
----------------------------------
1   A        Vendor         Add1
2   B        Vendor         Add2
3   C        Purchaser      Add3
4   D        Agent          Add4

Required result:

Vendor Name    Vendor Address    Vendor 1 Name    Vendor 1 Address  Purchaser Name    Purchaser Address    Agent Name    Agent Address
A              Add1              B                Add2              C                 Add3                 D             Add4

How can I build the result in which column names will create dynamic like if first value is vendor and if second time vendor will come then it will display look like vendor 1 + Name or Address?

As an alternative you can do this with crosstabs. The performance of crosstabs will generally be better than PIVOT especially as the number of rows increases. http://www.sqlservercentral.com/articles/T-SQL/63681/ — Sean Lange, Sep 08 '15 at 15:33
@GordonLinoff I got the solution for rows convert into columns.. But I want column name should be dynamic... please advice. — Jatin Gadhiya, Sep 08 '15 at 15:51
@SeanLange..... is it possible make column name dynamic if value are duplicate? — Jatin Gadhiya, Sep 08 '15 at 15:52
possible duplicate of [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Tab Alleman, Sep 08 '15 at 15:56
@JatinGadhiya sure. Take a look at the dynamic version of cross tabs. http://www.sqlservercentral.com/articles/Crosstab/65048/ — Sean Lange, Sep 08 '15 at 16:03
Hello I've update the question. Can you please review.. because some of guys try to close my question.\ — Jatin Gadhiya, Sep 08 '15 at 16:19
So, no matter how many rows there are in your data, you want your query results to have just one row? — Tab Alleman, Sep 08 '15 at 17:20
@TabAlleman, Yes it will only one row not an issue. Please advice for column name dynamic and display look like vendor name, vendor address, vendor1 name, vendor1 address... etc — Jatin Gadhiya, Sep 08 '15 at 18:13
Frankly, I wouldn't attempt what you want in a single query. I would write a multi-step script that loops through your data and builds a dynamic sql string based on your logic. Then a final step executes the query. — Tab Alleman, Sep 08 '15 at 20:39

ProblemSolver · Answer 1 · 2015-09-09T09:08:27.083

You could use following table:

                CREATE TABLE TEMP (
                DATE DATETIME
                ,category VARCHAR(3)
                ,amount MONEY
                )

            INSERT INTO TEMP
            VALUES (
                '1/1/2012'
                ,'ABC'
                ,1000.00
                )

            INSERT INTO TEMP
            VALUES (
                '2/1/2012'
                ,'DEF'
                ,500.00
                )

            INSERT INTO TEMP
            VALUES (
                '2/1/2012'
                ,'GHI'
                ,800.00
                )

            INSERT INTO TEMP
            VALUES (
                '2/10/2012'
                ,'DEF'
                ,700.00
                )

            INSERT INTO TEMP
            VALUES (
                '3/1/2012'
                ,'ABC'
                ,1100.00
                )
                    DECLARE @cols AS NVARCHAR(MAX),
                    @query  AS NVARCHAR(MAX);

                SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
                            FROM temp c
                            FOR XML PATH(''), TYPE
                            ).value('.', 'NVARCHAR(MAX)') 
                        ,1,1,'')

                set @query = 'SELECT date, ' + @cols + ' from 
                            (
                                select date
                                    , amount
                                    , category
                                from temp
                           ) x
                            pivot 
                            (
                                 max(amount)
                                for category in (' + @cols + ')
                            ) p '


                execute(@query)

                drop table temp

Can you give me full example.. so i can compare with my tables.. or i can create table matching with your script. — Jatin Gadhiya, Sep 08 '15 at 16:26
create table temp ( date datetime, category varchar(3), amount money ) insert into temp values ('1/1/2012', 'ABC', 1000.00) insert into temp values ('2/1/2012', 'DEF', 500.00) insert into temp values ('2/1/2012', 'GHI', 800.00) insert into temp values ('2/10/2012', 'DEF', 700.00) insert into temp values ('3/1/2012', 'ABC', 1100.00) — ProblemSolver, Sep 08 '15 at 19:35
Thank you.. I've try with your example and its run successfully.. but I think you didn't understand my requirement. Can you please review my updated question or Can i explain again? — Jatin Gadhiya, Sep 09 '15 at 14:38
Why doesn't this solution fit your requirement? @JatinGadhiya — Tab Alleman, Aug 09 '18 at 18:24

How convert rows to columns in SQL Server

1 Answers1