Row as column in sql server

Question

I have a table like below in SQL Server 2008

location       LOB               UnitName
----------     ---------         -------------------
Chennai         Health           UnitB
Mumbai          Health           UnitB
Pune            Health           UnitA,UnitB
Chennai         Motor            UnitB
Mumbai          Motor            UnitB
Pune            Motor            UnitB,UnitC
Trivandum       Motor            UnitC

And I am expecting the result like below..

Location       Health            Motor
---------      --------          --------
Chennai        UnitB             UnitB
Mumbai         UnitB             UnitB
Pune           UnitA,UnitB       UnitB,UnitC
Trivandum                        UnitC

I need a query to display like this. Can anyone help me to achieve this??

We understand you want the solution, but what have you tried? — Murtaza, Aug 18 '14 at 10:11
I tried this.. SELECT Location, LOB FROM [VW_UnitVIew2] PIVOT ( UnitName FOR LOB in () ) AS PivotTable ORDER BY Location; — Merin, Aug 18 '14 at 10:12
Have a look at this link http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql — Ajay Chauhan, Aug 18 '14 at 10:13
[Using PIVOT](http://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx) — huMpty duMpty, Aug 18 '14 at 10:19

Farrokh · Answer 1 · 2014-08-18T10:23:45.837

0

try this:

SELECT DISTINCT location,
        ISNULL((SELECT UnitName FROM table_name t1 WHERE t1.location=t.location AND t1.LOB= 'Health'),'') AS Health,
        ISNULL((SELECT UnitName FROM table_name t2 WHERE t2.location=t.location AND t2.LOB= 'Motor'),'') AS Motor
FROM table_name t

edited Aug 18 '14 at 10:23

answered Aug 18 '14 at 10:15

Farrokh

1,167
1
7
18

We can not say Health and Motor will be the LOB. It would be dynamic. What is the solution for dynamic data? – Merin Aug 18 '14 at 10:29
oh, i think thay are constant and not dynamic, it was better to explain this in your question, i will think on it ;) – Farrokh Aug 18 '14 at 10:32

Dimt · Answer 2 · 2014-08-18T10:38:30.250

0

To pivot a table you need to use aggregate function on pivoted values which is missing in code in your comment. In your case you can use MAX()

SELECT * 
FROM(
    SELECT location, LOB, UnitName
    FROM @t
) AS t
PIVOT(
    MAX(UnitName)
    FOR LOB IN([Health], [Motor])
) AS pivotTable

For dynamic columns try the below:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
       + QUOTENAME(LOB)
FROM (SELECT DISTINCT LOB FROM @t) AS Courses

SET @DynamicPivotQuery = 

'SELECT * 
FROM(
    SELECT location, LOB, UnitName
    FROM @t
) AS t
PIVOT(
    MAX(UnitName)
    FOR LOB IN(' + @ColumnName + ')
) AS pivotTable
ORDER BY Location DESC'

EXEC sp_executesql @DynamicPivotQuery

edited Aug 18 '14 at 10:38

answered Aug 18 '14 at 10:18

Dimt

2,278
2
20
26

I will work if LOB is Health and Motor. We can't say that this two LOB only it would be dynamic data – Merin Aug 18 '14 at 10:23
We can not say Health and Motor will be the LOB. It would be dynamic. What is the solution for dynamic data? – Merin Aug 18 '14 at 10:27
You need to look for dynamic SQL, that is to build the query string and substitute IN clause with the variable containing column names to be pivoted http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/ – Dimt Aug 18 '14 at 10:30

Murtaza · Answer 3 · 2014-08-18T10:47:06.373

The most simplest way which i do is this below query:

    SELECT DISTINCT MAIN.location, ISNULL(HEALTH.UnitName, '') as [HEALTH], ISNULL(Motor.UnitName, '') as [Motor]
    FROM 
    (Select location from unitlocation ) AS MAIN 
    LEFT OUTER JOIN 
    (SELECT location, UnitName from unitlocation where LOB = 'Health') AS HEALTH ON MAIN.location = HEALTH.location 
    LEFT OUTER JOIN 
    (SELECT location, UnitName from unitlocation where LOB = 'Motor') AS Motor ON MAIN.location = Motor.location

BELOW IS THE WORKING DEMO CLICK HERE

DYNAMIC GENERATION

PLEASE FIND THE WORKING DEMO FOR DYNAMIC COLUMN NAMES:

DECLARE @cols VARCHAR(MAX)
;WITH CTE
AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY [LOB] ORDER BY [LOB]) AS RowNbr, 
        LOB,
        UNITNAME
    FROM
        unitlocation
)
SELECT @cols=STUFF
(
    (
        SELECT
            ',' +QUOTENAME([LOB])
        FROM
            CTE
        WHERE
            CTE.RowNbr=1
    FOR XML PATH('')
    )
,1,1,'')


DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        unitlocation.location,
        unitlocation.LOB,
        unitlocation.UnitName
    FROM
        unitlocation
) AS p
PIVOT
(
    MAX(UnitName)
    FOR LOB IN ('+@cols+')
) AS pvt'

EXECUTE(@query)

WORKING FIDDLE HERE

Row as column in sql server

3 Answers3