SQL Server table manipulation

Question

I am really new here. Currently working on SQL Server 2012.

I have table A of the following structure (records are a subset of the full data table):

CREATE TABLE [dbo].[TABLE_A](
    [ASSET ID] [float] NULL,
    [TASK ID] [float] NULL,
    [IN_YEAR ] [float] NULL,
    [IN_WEEK] [float] NULL,
    [FLAG] [nvarchar](1) NULL
) ON [PRIMARY]

INSERT INTO TABLE_A
    ([ASSET ID], [TASK ID], [IN_YEAR], [IN_WEEK], [FLAG])
VALUES
    (1, 1, 2015, 19, 'N'),
    (1, 1, 2015, 20, 'Y'),
    (1, 1, 2015, 21, 'N'),
    (1, 2, 2015, 19, 'Y'),
    (1, 2, 2015, 20, 'N'),
    (1, 2, 2015, 21, 'N'),
    (2, 1, 2015, 19, 'N'),
    (2, 1, 2015, 20, 'N'),
    (2, 1, 2015, 21, 'N')
;

In table A, we always have the same number of weeks/year for each unique combination of asset ID and task ID.

And would like to translate it into table B structure:

+----------+---------+--------------+--------------+--------------+
| Asset ID | Task ID | 2015–WEEK 19 | 2015–WEEK 20 | 2015–WEEK 21 |
+----------+---------+--------------+--------------+--------------+
|        1 |       1 | N            | Y            | N            |
|        1 |       2 | Y            | N            | N            |
|        2 |       1 | N            | N            | N            |
+----------+---------+--------------+--------------+--------------+

Any thoughts?

You might find your answer [here](http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql)... — Peter Schneider, May 06 '15 at 15:53

John Bell · Accepted Answer · 2015-05-06T16:18:40.153

0

Give this code a try for size and see how you get on. It first takes all the unique year/weeks and then creates a select list. Then using dynamic T-SQL it creates a pivot table to show your desired layout:

SELECT DISTINCT CAST(IN_YEAR AS VARCHAR) + '-WEEK '+CAST(IN_WEEK AS VARCHAR) AS VALS
INTO #VALS
FROM TABLE_A

DECLARE @VALS NVARCHAR(500)
DECLARE @SQL NVARCHAR(MAX)

SELECT @VALS = COALESCE(@VALS+', ','') + '[' + VALS + ']' FROM #VALS

SET @SQL = '
;WITH CTE AS (
SELECT [ASSET ID], [TASK ID], CAST(IN_YEAR AS VARCHAR) + ''-WEEK ''+CAST(IN_WEEK AS VARCHAR) AS YEARWEEK, FLAG
FROM TABLE1)
SELECT [ASSET ID], [TASK ID], '+@VALS+'
FROM CTE
PIVOT(MAX(FLAG) FOR YEARWEEK IN ('+@VALS+')) PIV'
PRINT @SQL
EXEC (@SQL)

Working fiddle here.

edited May 06 '15 at 16:18

answered May 06 '15 at 16:06

John Bell

2,350
1
14
23

Hi Johnny, thanks so much for the help. It seems to work with a small data set you got, but the full dataset has almost 1m records. ASSET ID, TASK ID, IN_YEAR, IN_WEEK are of type int, FLAG is nvarchar(1). – Sheng Liu May 06 '15 at 16:44
So it doesn't work on your full dataset? Does it throw any errors? – John Bell May 07 '15 at 07:51
HI Johnny, after making some minor updates, it works like a charm! Thank you very much. And I get to explore dynamic SQL which was new to me. – Sheng Liu May 07 '15 at 14:55

SQL Server table manipulation

1 Answers1