CODEWITHSUNDEEP
sqlsql-server-2008pivot
Data_Sophist
Data_Sophist

Reputation: 23

Pivot 3 Columns in SQL

HI I have a table having 1.4 M records. The table structure is as follows

Col_1 INT, -- this column has post Codes
Col_2 VARCHAR, -- This Column Shop Names 
Col_3 MONEY, -- this column has distance from every Shop Name to post code with in 50 KM radius.

Total distinct post shop names are = 350
Total Distinct post codes are = 50,000

I want to pivot data so that it easily consumed in excel (in sort of matrix form) where 1 column has all the post codes and 1st row has all the shop names and at the intersection of post code and shop name is the corresponding distance. i can pivot the data using powerpivot but it crashes every time due to num of records.

Table Ex:

1. Col_1    Col_2  Col_3
2. 521025   Rams   7
3. 526256   Rams   8
4. 521025   SPhi   9
5. 526252   pho    10

Output Required

1. PostCode Rams  Sphi  Pho
2. 521025   7     9     0
3. 526256   8     0     10

How can i achieve this in SQL Any help appreciated

Upvotes: 2

Views: 189

Answers (2)

Taryn
Taryn

Reputation: 247810

This type of transformation from rows to columns is known as a PIVOT. In SQL Server 2005+ a function was added that can perform this rotation.

If you know the values ahead of time, then you can hard-code them using a static pivot:

select col_1, 
  IsNull(Rams, 0) Rams, 
  IsNull(Sphi, 0) Sphi, 
  IsNull(pho, 0) Pho
from
(
  select col_1, col_2, col_3
  from yourtable
) src
pivot
(
  sum(col_3)
  for col_2 in (Rams, Sphi, Pho)
) piv

See SQL Fiddle with Demo.

But if you have an unknown number of values that you need to transpose into columns, then you can use dynamic sql to perform this:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(col_2) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsNull = STUFF((SELECT distinct ', IsNull(' + QUOTENAME(col_2)+', 0) as '+QUOTENAME(col_2)
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT col_1,' + @colsNull + ' from 
             (
                select col_1, col_2, col_3
                from yourtable
            ) x
            pivot 
            (
                sum(col_3)
                for col_2 in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

The result of both is:

|  COL_1 | PHO | RAMS | SPHI |
------------------------------
| 521025 |   0 |    7 |    9 |
| 526252 |  10 |    0 |    0 |
| 526256 |   0 |    8 |    0 |

Upvotes: 1

TechDo
TechDo

Reputation: 18649

Please check

SELECT 
    Col_1 AS PostCode, 
    ISNULL(Rams, 0) Rams, 
    ISNULL(Sphi, 0) Sphi, 
    ISNULL(Pho, 0)Pho
FROM(
    SELECT Col_1, Col_2, Col_3 FROM YourTable
)UP
PIVOT(SUM(Col_3) FOR Col_2 IN (Rams, Sphi, Pho)) AS PVT

Upvotes: 0

Related Questions