CODEWITHSUNDEEP
sqlsql-servert-sql
Ninja
Ninja

Reputation: 376

SQL: Convert Data For One Column To Multiple Columns

I have tried looking for a solution to my problem using things such as Pivot, but they do not seem to give the output for which I am looking as they appear to map specific values in a row to a column.

I have two columns, the first which contains a "key" field and the second which is changing data. e.g.

╔══════════╦══════════════════╗
║ Project  ║     Location     ║
╠══════════╬══════════════════╣
║ ProjectA ║ \\Server1\Share1 ║
║ ProjectA ║ \\Server2\Share1 ║
║ ProjectB ║ \\Server6\Share2 ║
║ ProjectB ║ \\Server1\Share2 ║
║ ProjectB ║ \\Server2\Share3 ║
║ ProjectC ║ \\Server8\Share2 ║
║ ProjectD ║ \\Server5\Share9 ║
║ ProjectD ║ \\ServerX\ShareY ║
╚══════════╩══════════════════╝

The output that I am trying to achieve is as follows:

╔══════════╦══════════════════╦══════════════════╦══════════════════╦═════════╦══════════╦═════════╗
║ Project  ║     Column1      ║     Column2      ║     Column3      ║ Column4 ║ Column5  ║ ColumnX ║
╠══════════╬══════════════════╬══════════════════╬══════════════════╬═════════╬══════════╬═════════╣
║ ProjectA ║ \\Server1\Share1 ║ \\Server2\Share1 ║ NULL             ║ NULL    ║ NULL     ║         ║
║ ProjectB ║ \\Server1\Share2 ║ \\Server2\Share3 ║ \\Server6\Share2 ║ NULL    ║ NULL     ║         ║
║ ProjectC ║ \\Server8\Share2 ║ NULL             ║ NULL             ║ NULL    ║ NULL     ║         ║
║ ProjectD ║ \\Server5\Share9 ║ \\ServerX\ShareY ║ NULL             ║ NULL    ║ NULL     ║         ║
╚══════════╩══════════════════╩══════════════════╩══════════════════╩═════════╩══════════╩═════════╝

If there is no data for the column then it will be NULL.

The number of distinct values in the location column is dynamic and the desired output is a generic column name, with the distinct location value beside the corresponding Project value.

Hopefully someone can help me with this problem as it is driving me mad!

Thanks in advance.

Ninja

Upvotes: 3

Views: 7878

Answers (2)

Ninja
Ninja

Reputation: 376

Credit goes to @lad2025.

I am using Server2005 so these are the modifications that I had to make to get this to work for me:

  • Couldn't declare and assign a variable in the same line. @max INT = 0;
  • CONCAT function doesn't exist so replaced with + between the parameters. ', ' + '[' + convert(varchar, c.n) + '] AS Column' + c.n
  • c.n is of type bigint so I had to CONVERT it to VARCHAR - CONVERT(VARCHAR, c.n)
  • When generating the table on which to pivot, a GROUP BY needs to be provided otherwise the data appears in a strange column number.

Without GROUP BY Without the GROUP BY as more data is in the initial #mytable the results display in columns that are not sequential.

╔══════════╦══════════════════╦══════════════════╦══════════════════╦══════╦══════════════════╦══════╗
║ Project  ║       Col1       ║       Col2       ║       Col3       ║ Col4 ║       Col5       ║ Col6 ║
╠══════════╬══════════════════╬══════════════════╬══════════════════╬══════╬══════════════════╬══════╣
║ ProjectA ║ \\Server1\Share1 ║ \\Server2\Share1 ║ NULL             ║ NULL ║ NULL             ║ NULL ║
║ ProjectB ║ \\Server1\Share2 ║ \\Server2\Share3 ║ \\Server6\Share2 ║ NULL ║ NULL             ║ NULL ║
║ ProjectC ║ \\Server8\Share2 ║ NULL             ║ NULL             ║ NULL ║ NULL             ║ NULL ║
║ ProjectD ║ NULL             ║ \\Server5\Share9 ║ NULL             ║ NULL ║ \\ServerX\ShareY ║ NULL ║
╚══════════╩══════════════════╩══════════════════╩══════════════════╩══════╩══════════════════╩══════╝

With GROUP BY As it should be.

╔══════════╦══════════════════╦══════════════════╦══════════════════╦══════╦══════╦══════╗
║ Project  ║       Col1       ║       Col2       ║       Col3       ║ Col4 ║ Col5 ║ Col6 ║
╠══════════╬══════════════════╬══════════════════╬══════════════════╬══════╬══════╬══════╣
║ ProjectA ║ \\Server1\Share1 ║ \\Server2\Share1 ║ NULL             ║ NULL ║ NULL ║ NULL ║
║ ProjectB ║ \\Server1\Share2 ║ \\Server2\Share3 ║ \\Server6\Share2 ║ NULL ║ NULL ║ NULL ║
║ ProjectC ║ \\Server8\Share2 ║ NULL             ║ NULL             ║ NULL ║ NULL ║ NULL ║
║ ProjectD ║ \\Server5\Share9 ║ \\ServerX\ShareY ║ NULL             ║ NULL ║ NULL ║ NULL ║
╚══════════╩══════════════════╩══════════════════╩══════════════════╩══════╩══════╩══════╝

Data:

CREATE TABLE #mytable(
   Project  VARCHAR(80) NOT NULL 
  ,Location VARCHAR(160) NOT NULL
);
INSERT INTO #mytable(Project,Location) VALUES ('ProjectA','\\Server1\Share1');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectA','\\Server2\Share1');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectB','\\Server6\Share2');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectB','\\Server1\Share2');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectB','\\Server2\Share3');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectC','\\Server8\Share2');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectD','\\Server5\Share9');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectD','\\ServerX\ShareY');

Query:

DECLARE @cols  NVARCHAR(MAX),
        @cols_piv NVARCHAR(MAX),
        @query NVARCHAR(MAX)
        ,@max  INT;

SELECT @max = MAX(c)
FROM (
  SELECT Project, COUNT(DISTINCT Location) AS c
  FROM #mytable
  GROUP BY Project) AS s;

SET @cols = STUFF(     
            (SELECT ', ' +  '[' + convert(varchar, c.n) + '] AS Column' + convert(varchar, c.n)
            FROM  ( SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY n)AS c(n)
            WHERE c.n <= @max
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @cols_piv = STUFF(
            (SELECT ',' +  '[' + convert(varchar, c.n) + ']'
            FROM  ( SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY n)AS c(n)
            WHERE c.n <= @max
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');        

set @query = N'SELECT Project, ' + @cols + ' from 
            (
                select Project, Location,
                [rn] = RANK() OVER (PARTITION BY Project ORDER BY Location)
                from #mytable
                GROUP BY Project, Location
            ) x
            pivot 
            (
                max(Location)
                for rn in (' + @cols_piv + ')
            ) p ';

EXEC [dbo].[sp_executesql]
    @query;

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

Warning:

This solution assume that it will be max 6 column, you can add more for example up to 20.

LiveDemo

Data:

CREATE TABLE #mytable(
   Project  VARCHAR(80) NOT NULL 
  ,Location VARCHAR(160) NOT NULL
);
INSERT INTO #mytable(Project,Location) VALUES ('ProjectA','\\Server1\Share1');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectA','\\Server2\Share1');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectB','\\Server6\Share2');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectB','\\Server1\Share2');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectB','\\Server2\Share3');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectC','\\Server8\Share2');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectD','\\Server5\Share9');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectD','\\ServerX\ShareY');
INSERT INTO #mytable(Project,Location) VALUES ('ProjectD','\\ServerX\ShareY');

Query:

WITH cte AS
(
  SELECT Project, Location,
    [rn] = RANK() OVER (PARTITION BY Project ORDER BY Location)
  FROM #mytable
)
SELECT
   Project
  ,Column1 = MAX(CASE WHEN rn = 1 THEN Location ELSE NULL END)
  ,Column2 = MAX(CASE WHEN rn = 2 THEN Location ELSE NULL END)
  ,Column3 = MAX(CASE WHEN rn = 3 THEN Location ELSE NULL END)
  ,Column4 = MAX(CASE WHEN rn = 4 THEN Location ELSE NULL END)
  ,Column5 = MAX(CASE WHEN rn = 5 THEN Location ELSE NULL END)
  ,Column6 = MAX(CASE WHEN rn = 6 THEN Location ELSE NULL END)
  -- ....
  --  ,ColumnX = MAX(CASE WHEN rn = X THEN Location ELSE NULL END)
FROM cte
GROUP BY Project;

EDIT:

Truly generic solution that uses Dynamic-SQL and generates Pivoted column list:

LiveDemo2

DECLARE @cols  NVARCHAR(MAX),
        @cols_piv NVARCHAR(MAX),
        @query NVARCHAR(MAX)
        ,@max  INT = 0;

SELECT @max = MAX(c)
FROM (
  SELECT Project, COUNT(DISTINCT Location) AS c
  FROM #mytable
  GROUP BY Project) AS s;


SET @cols = STUFF(     
            (SELECT ',' +  CONCAT('[',c.n, '] AS Column',c.n, ' ')
            FROM  ( SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY n)AS c(n)
            WHERE c.n <= @max
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SET @cols_piv = STUFF(
            (SELECT ',' +  CONCAT('[',c.n, '] ')
            FROM  ( SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY [object_id]) FROM sys.all_objects ORDER BY n)AS c(n)
            WHERE c.n <= @max
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');        

-- SELECT @cols;

set @query = N'SELECT Project, ' + @cols + ' from 
            (
                select Project, Location,
                [rn] = RANK() OVER (PARTITION BY Project ORDER BY Location)
                from #mytable
            ) x
            pivot 
            (
                max(Location)
                for rn in (' + @cols_piv + ')
            ) p ';

-- SELECT @query; 

EXEC [dbo].[sp_executesql]
    @query;

Upvotes: 4

Related Questions