Pivot\Unpivot issues?

Question

I have the following data

Col1,  Col2,  Col3, Col4, Col5                         
1, P,  W, M, adhfggh                 
1, P,  W, M, fdasdfd                    
1, P,  W, M, retretre

So, I would like to get this

Col1,  Col2,  Col3, Col4, ColA, ColB, ColC         
1, P,  W, M, adhfggh, fdasdfd, retretre

I should try something like this, but not sure what to put in brackets

select Col1, Col2, Col3, Col4, Col5 from tableA         
Unpivot 
( Col1,  Col2,  Col3, Col4 for Col5 in (, , ) ) as UnPvt

I appreciate for any help.

score 0 · Accepted Answer · edited May 23 '17 at 12:19

0

You didn't specify what version of SQL you are using so this is T-SQL and will work on any SQL Server after 2008.

This dynamic pivot was altered from this answer & will format to your requirements. You might also wish to look into the STUFF function

    CREATE TABLE #T
                  (  Col1 int 
                    ,Col2 [nchar](3) 
                    ,Col3 [nchar](3)
                    ,Col4 [nchar](3)
                    ,Col5 [nchar](10)
                    )

    Insert Into #T
    Values
    (1,'P','W','M','adhfggh')
    ,(1,'P','W','M','fdasdfd')
    ,(1,'P','W','M','retretre');

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

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

    set @query = 'SELECT Col1,Col2,Col3,Col4, ' + @cols + ' from 
        (
            select  Col1,   
                    Col2,  
                    Col3,
                    Col4,
                    Col5                       
            from #T     
        ) x
        pivot 
        (
             max(Col5)
            for Col5 in (' + @cols + ')
        ) p '


    execute(@query)

    drop table #T

edited May 23 '17 at 12:19

Community

1
1

answered Nov 08 '16 at 03:54

M O'Connell

487
5
18

Thanks for help but I am wondering is it possible to do just using the query.... SELECT WHERE FROM without variables (T-SQL)... – piva1970 Nov 08 '16 at 05:57
If you want to pivot `Col5` you need to know how many rows there are. If you'll always have the same number of rows eg."3 rows", then yes it's possible. If you will have more/less than 3 rows then you need to use dynamic SQL & variables are required – M O'Connell Nov 08 '16 at 06:03
Col5 can have 1 to 3 rows... Could you please show me how it would look in case that always have 3 rows. Let see the example where we have a Null value for other two columns in case that there is only one row that has a value. – piva1970 Nov 08 '16 at 06:34
Actually, I will always have 3 columns ...ColA, ColB, ColC but sometimes they will have Null values dependent how many rows do I have... Thanks – piva1970 Nov 08 '16 at 06:44
I'm not at a computer so examples are limited. What you need is a pivot on `Col5`, for this you need a to know how many ROWS to pivot into columns. That is what goes into `for Col5 in ('value1', 'value2','value3') `. It's difficult to explain with out examples but try running `Select @cols` (you won't need a FROM) after the `Set @cols =` and see how it works – M O'Connell Nov 08 '16 at 07:02
1

There is no version of the SQL standard named "SQL 2012" do you happen to refer to "SQL **Server** 2012"? – Nov 08 '16 at 07:56
Yes, T-SQL on SQL Server 2012. However this should work on anything after 2008 when the STUFF function was introduced. – M O'Connell Nov 08 '16 at 10:47
user3240704 is it possible to have with a just regular without a solution with @cols and STUFF function? – piva1970 Nov 08 '16 at 15:11
I'm confused as to why you don't want to use the available functions. Functions are introduced to help with complex problems like yours. But if you are more comfortable with Windows functions, [this](http://stackoverflow.com/a/30633322/3240704) may also work. I'm not at a computer so you can adopt as required – M O'Connell Nov 08 '16 at 20:27
We want to keep everything within SSIS instead of building functions on database. – piva1970 Nov 08 '16 at 22:20
Thanks! user3240704 I tried your STUFF (function above). I got error because the value of the new column (ColA) is repeated bit I am curious am I able to name the new columns (in this case ColA, ColB, ColC) as whatever names I want. – piva1970 Nov 08 '16 at 22:44
You confusing [user defined functions](https://msdn.microsoft.com/en-us/library/ms191320.aspx) with [built in functions](https://msdn.microsoft.com/en-us/library/ms174318.aspx). The STUFF and PIVOT functions are MS SQL built in functions since 2008&2005, there is no building anything in the DB. – M O'Connell Nov 08 '16 at 22:44
@piva1970 you need to try and understand how it's working, then you can alter it as you require. You asked a question and provided sample data, the response answers the question asked. You can of course alter the code to suit your needs but you'll need to understand the code or else open a new question – M O'Connell Nov 08 '16 at 22:50
I just started using SSIS. So, can you explain me how in SSIS can I apply the code above...Thanks – piva1970 Nov 08 '16 at 23:42
I dont use SSIS, you'll need to [read the documentation](https://msdn.microsoft.com/en-us/library/ms169917.aspx) – M O'Connell Nov 08 '16 at 23:44

Pivot\Unpivot issues?

1 Answers1