SQL Server : show rows as columns / Pivot

Question

I have a SQL Server 2008 Express table like this:

rno   gid   uid   dat          origamt   disamt
-----------------------------------------------
1     AA    a     12-05-2016    200       210
2     AA    b     12-05-2016    300       305
3     AA    c     12-05-2016    150       116
4     BB    a     12-05-2016    120       125
5     BB    c     12-05-2016    130       136
6     CC    a     12-05-2016    112       115
7     CC    b     12-05-2016    135       136

and so on for different dates

I want to show it like this:

sno     dat       gid   a_orig   a_dis   b_orig  b_dis   c_orig   c_dis .....
1    12-05-2016   AA    200      210     300     305     150      116
2    12-05-2016   BB    120      125     0       0       130      136
3    12-05-2016   CC    112      115     135     136     0        0

NOTE: the values of uid are not fixed, they may vary dynamically, so, a_orig, a_dis, b_orig, b_dis, etc cannot be hardcoded into SQL.

NOTE: around 300 rows are expected on each date due to the cartesian product of gid and uid. and I will search datewise by implementing the LIKE clause since datatype of dat column is varchar(50).

Note: I would prefer datatype of origamt and disamt to be varchar(50) instead of Decimal(18, 0) but it is not compulsion.

I have tried to use PIVOT by taking reference from several articles posted here on stackoverflow and other website but couldn't get the work done completely.

Here is what I tried and got almost fine results with fixed uid and only fetched origamt:

select *
from 
(
  select gid, uid, dat, origamt
  from vamounts
) as src
pivot
(
  sum(origamt)
  for uid IN ( a, b )
) as piv;

Kindly help me with the least bulky possible solution for this problem. I will prefer least lines of code and least complexity.

Answer 1

Errr, no. You can't generate your desired table using SQL. This isn't a valid pivot table.

"the values of uid are not fixed, they may vary dynamically, so, a_orig, a_dis, b_orig, b_dis, etc cannot be hardcoded into SQL."

Sorry, this is also not possible. You must specify the exact values to be placed as the column headers. Whenever you write a SELECT statement, you must specify the names of the columns (fields) which you'll be returning. There's no way around this.

However, below are the steps required to create a "valid" SQL Server pivot table from your data:

I've got to admit, when I recently had to write my first PIVOT in SQL Server, I also Googled like mad, but didn't understand how to write it.

However, I eventually worked out what you need to do, so here's the step-by-step guide that you won't find anywhere else..!

(Readers can easily adapt these instructions, to use with your own data !)

1. Create your sample data

If you expect readers to reply to your Question, you should at least give them the SQL to create your sample data, so they have something to work off.

So, here's how I would create the data shown in your question:

CREATE TABLE tblSomething
(
    [gid] nvarchar(100),
    [uid] nvarchar(100),
    [dat] datetime,
    [origamt] int,
    [disamt] int
)
GO

INSERT INTO tblSomething VALUES ('AA', 'a', '2016-05-12', 200, 210)
INSERT INTO tblSomething VALUES ('AA', 'b', '2016-05-12', 300, 305)
INSERT INTO tblSomething VALUES ('AA', 'c', '2016-05-12', 150, 116)
INSERT INTO tblSomething VALUES ('BB', 'a', '2016-05-12', 120, 125)
INSERT INTO tblSomething VALUES ('BB', 'c', '2016-05-12', 130, 136)
INSERT INTO tblSomething VALUES ('CC', 'a', '2016-05-12', 112, 115)
INSERT INTO tblSomething VALUES ('CC', 'b', '2016-05-12', 135, 136)
GO

2. Write a SQL Query which returns exactly three columns

The first column will contain the values which will appear in your PIVOT table's left-hand column.

The second column will contain the list of values which will appear on the top row.

The values in the third column will be positioned within your PIVOT table, based on the row/column headers.

Okay, here's the SQL to do this:

SELECT [gid], [uid], [origamt]
FROM tblSomething

This is the key to using a PIVOT. Your database structure can be as horribly complicated as you like, but when using a PIVOT, you can only work with exactly three values. No more, no less.

So, here's what that SQL will return. Our aim is to create a PIVOT table containing (just) these values:

3. Find a list of distinct values for the header row

Notice how, in the pivot table I'm aiming to create, I have three columns (fields) called a, b and c. These are the three unique values in your [uid] column.

So, to get a comma-concatenated list of these unique values, I can use this SQL:

DECLARE @LongString nvarchar(4000)

SELECT @LongString = COALESCE(@LongString + ', ', '') + '[' + [uid] + ']' 
FROM [tblSomething]
GROUP BY [uid]

SELECT @LongString AS 'Subquery'

When I run this against your data, here's what I get:

Now, cut'n'paste this value: we'll need to place it twice in our overall SQL SELECT command to create the pivot table.

4. Put it all together

This is the tricky bit.

You need to combine your SQL command from Step 2 and the result from Step 3, into a single SELECT command.

Here's what your SQL would look like:

SELECT [gid],
        --  Here's the "Subquery" from part 3
        [a], [b], [c]
FROM (
    --  Here's the original SQL "SELECT" statement from part 2
    SELECT [gid], [uid], [origamt]
    FROM tblSomething

) tmp ([gid], [uid], [origamt])
pivot (
    MAX([origamt]) for [uid] in (
        --  Here's the "Subquery" from part 3 again
        [a], [b], [c]
    )
) p

... and here's a confusing image, which shows where the components come from, and the results of running this command.

As you can see, the key to this is that SELECT statement in Step 2, and putting your three chosen fields in the correct place in this command.

And, as I said earlier, the columns (fields) in your pivot table come from the values obtained in step 3:

[a], [b], [c]

You could, of course, use a subset of these values. Perhaps you just want to see the PIVOT values for [a], [b] and ignore [c].

Phew !

So, that's how to create a pivot table out of your data.

I will prefer least lines of code and least complexity.

Yeah, good luck on that one..!!!

5. Merging two pivot tables

If you really wanted to, you could merge the contents of two such PIVOT tables to get the exact results you're looking for.

This is easy enough SQL for Shobhit to write himself.

Answer 2

A join might help

declare @t table (rno int,  gid varchar(2),  uid varchar(1),  dat varchar(10), origamt int,  disamt int)
insert into @t
values 
(1,     'AA',    'a',     '12-05-2016',    200,       210),
(2 ,    'AA',    'b',     '12-05-2016',    300,       305),
(3  ,   'AA',    'c',     '12-05-2016',    150,       116),
(4 ,    'BB',    'a',     '12-05-2016',    120,       125),
(5 ,    'BB',    'c',     '12-05-2016',    130,       136),
(6 ,    'CC',    'a',     '12-05-2016',    112,       115),
(7 ,    'CC',    'b',     '12-05-2016',    135,       136)

    select   -- piv.*,piv2.*
             piv.gid,piv.dat
            ,piv.a as a_org
            ,piv2.a as a_dis
            ,piv.b as b_org
            ,piv2.b as b_dis
            ,piv.c as c_org
            ,piv2.c as c_dis
    from 
    (
      select gid, uid, dat, origamt
      from  @t
    ) as src
    pivot
    (
      sum(origamt)
      for uid IN ([a],[b],[c] )
    ) as piv
    join 
    (select piv2.*
    from 
    (
      select gid, uid, dat, disamt
      from  @t
    ) as src
    pivot
    (
      sum(disamt)
      for uid IN ([a],[b],[c] )
    ) as piv2
    ) piv2
    on piv2.gid = piv.gid and piv2.dat = piv.dat 

This is a POC you would have to use dynamic sql to deal with the variable number of uids. Let me know if you don't know how to use dynamic SQL and I'll work up an example for you.

Answer 3

You need dynamic SQL for this stuff.

At first create table with your data:

CREATE TABLE #temp (
    rno int, 
    gid nvarchar(10), 
    [uid] nvarchar(10), 
    dat date, 
    origamt int, 
    disamt int 
) 

INSERT INTO #temp VALUES 
(1,     'AA',    'a',     '12-05-2016',    200,       210),
(2,     'AA',    'b',     '12-05-2016',    300,       305),
(3,     'AA',    'c',     '12-05-2016',    150,       116),
(4,     'BB',    'a',     '12-05-2016',    120,       125),
(5,     'BB',    'c',     '12-05-2016',    130,       136),
(6,     'CC',    'a',     '12-05-2016',    112,       115),
(7,     'CC',    'b',     '12-05-2016',    135,       136)

And then declare variables with columns:

DECLARE @columns nvarchar(max), @sql nvarchar(max), @columns1 nvarchar(max), @columnsN nvarchar(max)
--Here simple columns like [a],[b],[c] etc
SELECT @columns =STUFF((SELECT DISTINCT ','+QUOTENAME([uid]) FROM #temp FOR XML PATH('')),1,1,'')
--Here with ISNULL operation ISNULL([a],0) as [a],ISNULL([b],0) as [b],ISNULL([c],0) as [c]
SELECT @columnsN = STUFF((SELECT DISTINCT ',ISNULL('+QUOTENAME([uid])+',0) as '+QUOTENAME([uid]) FROM #temp FOR XML PATH('')),1,1,'')
--Here columns for final table orig.a as a_orig, dis.a as a_dis,orig.b as b_orig, dis.b as b_dis,orig.c as c_orig, dis.c as c_dis
SELECT @columns1 = STUFF((SELECT DISTINCT ',orig.'+[uid] + ' as ' +[uid]+ '_orig, dis.'+[uid] + ' as ' +[uid]+ '_dis' FROM #temp FOR XML PATH('')),1,1,'')

And main query:

SELECT @sql = '
SELECT  orig.gid,
        orig.dat,
        '+@columns1+'
FROM (
        SELECT gid, dat, '+@columnsN+'
        FROM (
            SELECT gid, [uid], LEFT(dat,10) as dat, origamt
            FROM #temp
            ) as p
        PIVOT (
        SUM(origamt) FOR [uid] in ('+@columns+')
        ) as pvt
    ) as orig
LEFT JOIN (
        SELECT gid, dat, '+@columnsN+'
        FROM (
            SELECT gid, [uid], LEFT(dat,10) as dat, disamt
            FROM #temp
            ) as p
        PIVOT (
        SUM(disamt) FOR [uid] in ('+@columns+')
        ) as pvt
    ) as dis
    ON dis.gid = orig.gid and dis.dat = orig.dat'

EXEC(@sql)

Output:

gid dat         a_orig  a_dis   b_orig  b_dis   c_orig  c_dis
AA  2016-12-05  200     210     300     305     150     116
BB  2016-12-05  120     125     0       0       130     136
CC  2016-12-05  112     115     135     136     0       0