Reformat output to different column structure

Transact-SQL
1

I have an SQL Server 2014 database that stores data like this: We are assigning tasks to a week of a year. There can be 0 to any number of tasks per week and year. I would like to be able to format the table so it looks like the lower section here, with each taskID getting it's own column and so that weeks with no entries are still included.

I can format it in my application but presume it would be more efficient and perhaps easier to have it done in the db. Should I do the formatting in SQL or should I do it in application? What would the process be to do it in SQL if that would be preferable?

2

please post sample data by doing the following

create table #angate(year int, month int, taskid int)

insert into #angate
select 2016, 50, 5356 union
select 2016, 50, 86213

Not easy to answer your question with a image attachment alone.

3

here you go! thank you for your assitance!

create table #angate(year int, month int, TaskID int)

insert into #angate (year, month, TaskID) VALUES
(2016, 50, 5356),
(2016, 50, 86213),
(2016, 50, 55498),
(2016, 51, 131),
(2016, 51, 213156),
(2016, 51, 16516),
(2017, 1, 2313),
(2017, 1, 3155),
(2017, 2, 68),
(2017, 2, 684),
(2017, 4, 64),
(2017, 5, 5846),
(2017, 6, 5646)
4 
DECLARE @starting_year int
DECLARE @ending_year int
DECLARE @starting_week int
DECLARE @ending_week int
SET @starting_year = 2016
SET @starting_week = 50
SET @ending_year = 2017
SET @ending_week = 6

;WITH
cteTally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cteTally100 AS (
    SELECT TOP (52) ROW_NUMBER() OVER(ORDER BY c1.number) AS number
    FROM cteTally10 c1
    CROSS JOIN cteTally10 c2
)
SELECT
    year_tally,
    week_tally AS month,
    MAX(CASE WHEN row_num = 01 THEN TaskID END) AS TaskID01,
    MAX(CASE WHEN row_num = 02 THEN TaskID END) AS TaskID02,
    MAX(CASE WHEN row_num = 03 THEN TaskID END) AS TaskID03,
    MAX(CASE WHEN row_num = 04 THEN TaskID END) AS TaskID04,
    MAX(CASE WHEN row_num = 05 THEN TaskID END) AS TaskID05,
    MAX(CASE WHEN row_num = 06 THEN TaskID END) AS TaskID06,
    MAX(CASE WHEN row_num = 07 THEN TaskID END) AS TaskID07,
    MAX(CASE WHEN row_num = 08 THEN TaskID END) AS TaskID08,
    MAX(CASE WHEN row_num = 09 THEN TaskID END) AS TaskID09,
    MAX(CASE WHEN row_num = 10 THEN TaskID END) AS TaskID10    
FROM cteTally100 years
CROSS JOIN cteTally100 weeks
CROSS APPLY (
    SELECT @starting_year + years.number - 1 AS year_tally, weeks.number AS week_tally
) AS ca1
LEFT OUTER JOIN (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY year, month ORDER BY TaskID) AS row_num
    FROM #angate
) AS derived ON derived.month = week_tally
WHERE year_tally BETWEEN @starting_year AND @ending_year AND
    ((year_tally = @starting_year AND week_tally >= @starting_week) OR
     (year_tally = @ending_year AND week_tally <= @ending_week) OR
     (year_tally > @starting_year AND year_tally < @ending_year))
GROUP BY year_tally, week_tally
ORDER BY year_tally, week_tally
1 Like