turn value into column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

turn value into column

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2015-02-23 : 07:26:11

I have this table:

ProductionID   ResourceID    Time
------------   ----------    -----
WO001             200012      5
WO001             200011      1
WO002             200013      2
WO003             200012      3
WO003             200032      4

and I want it to look like

ProductionID   200011  200012  200013  200032
------------   ------  ------  ------  ------
WO001            1       5      NULL    NULL
WO002           NULL    NULL     2      NULL
WO003           NULL     3      NULL     4

I am looking for a smart solution without hardcoding the column names (e.g. CASE WHEN ResourceID = '200012' THEN Time END as ['200012'], etc...), as there can be up to 15 different resources involved; usually they aren't but I want to keep the result as slim as possible without tons of columns containing nulls.

Is this possible?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-23 : 07:51:45

what you are looking for is a dynamic sql pivot table. If you google for that you'll find plenty of examples.

pradeepbliss
Starting Member

28 Posts

Posted - 2015-02-23 : 08:35:24

Select productionid,[200011],[200012],[200013],[200032] from (select distinct productionid,resourceid,time from Test_Production) up pivot (sum(time) for resourceid in([200011],[200012],[200013],[200032])) as pvt

Result:

Productionid 200011 200012 200013 200032
WO001 1 5 NULL NULL
WO002 NULL NULL 2 NULL
WO003 NULL 3 NULL 4

pradeepbliss
Starting Member

28 Posts

Posted - 2015-02-23 : 08:43:46

quote:
Originally posted by pradeepbliss

Select productionid,[200011],[200012],[200013],[200032] from (select distinct productionid,resourceid,time from Test_Production) up pivot (sum(time) for resourceid in([200011],[200012],[200013],[200032])) as pvt

Result:

Productionid 200011 200012 200013 200032
WO001 1 5 NULL NULL
WO002 NULL NULL 2 NULL
WO003 NULL 3 NULL 4

If u look for dynamic pivot concept just replace the resourceid column values in select ...

barnabeck
Posting Yak Master

236 Posts

Posted - 2015-02-23 : 10:18:01

Cool! I did that and in the static version it works - nice! BUT once I try to replace the column values with the select expression something is not right with the syntax. I changed the Resource values to something more easy...

declare @Production table (Productionid nvarchar(6), ResourceID nvarchar(6), time int)

insert into @Production values ('WO001', 'A00012', 5)
insert into @Production values ('WO001', 'A00011', 1)
insert into @Production values ('WO002', 'A00013', 2)
insert into @Production values ('WO003', 'A00012', 3)
insert into @Production values ('WO003', 'A00032', 4)

Select productionid,A00011,A00012,A00013,A00032 
from (
select distinct productionid,resourceid,time from @Production) up pivot (sum(time) for resourceid in (A00011,A00012,A00013,A00032)) as pvt

Until here everything is fine. Now, replacing the resourceid -values with (select distinct ResourceID from @Production) would lead to:

Select productionid, (SELECT distinct ResourceID from @Production) 
from (
select distinct productionid,resourceid,time from @Production) up pivot (sum(time) for resourceid in ((SELECT distinct ResourceID from @Production))) as pvt

Management Studio points out syntax errors, marked red... It throws: Incorrect Syntax near '('. Expecting '.', ID, or Quoted_ID

How should that look like? I've been trying everything imaginable...

barnabeck
Posting Yak Master

236 Posts

Posted - 2015-02-23 : 12:21:12

Well, as I know now it's not that simple as pradeepbliss made me think... you have to take this expression and wrap it around the list of values that are going to be the headers of your query. Tricky stuff, very nicely explained in https://www.youtube.com/watch?v=uZGjHYS9lzI

pradeepbliss
Starting Member

28 Posts

Posted - 2015-02-24 : 01:20:30

quote:
Originally posted by barnabeck

Well, as I know now it's not that simple as pradeepbliss made me think... you have to take this expression and wrap it around the list of values that are going to be the headers of your query. Tricky stuff, very nicely explained in https://www.youtube.com/watch?v=uZGjHYS9lzI

DECLARE @query VARCHAR(4000)
DECLARE @years VARCHAR(2000)
SELECT @years = (select distinct
STUFF((SELECT distinct ', ' + CAST(resourceid AS VARCHAR(10))
FROM Test_Production
FOR XML PATH(''), TYPE)
.value('.','NVARCHAR(MAX)'),1,2,' ') List_Output )

SET @query =
'SELECT * FROM
(
SELECT productionid,resourceid,time
FROM Test_Production
)t
PIVOT (SUM(time) FOR resourceid
IN ('+@years+')) AS pvt'

EXECUTE (@query)

Here i didn't mention any static column's ,its deals with dynamic column ....

barnabeck
Posting Yak Master

236 Posts

Posted - 2015-02-24 : 08:16:15

Ok, I finally managed to make this work with some simple test query, but now that I adapt it to my real query something fails.

It throws the "Operand data type varchar is invalid for subtract operator" - error. I removed all critical parts, including the dynamical part of the pivot query in order to isolate the problem. Right now I have:

declare @ExecutePivotQuery nvarchar(max)
set @ExecutePivotQuery = '...my...static..pivot...query...'
execute sp_executesql @ExecutePivotQuery

and it STILL throws that error, although I can perfectly run the static pivot query that is embedded inside the single quotes?

Anybody has a clue?

I will post the static query, although I can't see why it should be related to that error as it can be executed seperately.

with Resource as 

(
SELECT a.ProductionID, a.FinishedDate, a.RessourceID, a.OPERATIONID, COALESCE(a.DiffDays,0) - COALESCE (b.DiffDays,0) as delta

from (

 SELECT ProductionID, DiffDays, RessourceID, OPERATIONID, FinishedDate,
		row_number() over(partition by ProductionID ORDER BY OPERATIONID) as ord
 from iq4bisprocess.FactOTDRessource
 ) a

 left outer join (
 SELECT ProductionID, DiffDays, row_number() over(partition by ProductionID ORDER BY OPERATIONID) as ord
 from iq4bisprocess.FactOTDRessource) b
 
 on a.ProductionID = b.ProductionID and a.ord = b.ord+1),

 Production as 
 (SELECT ProductionID, FinishedDate, DiffDaysToBackward
  FROM iq4bisprocess.FactOTDProduction where FinishedDate >= (SELECT TOP 1 FinishedDate 
                         from iq4bisprocess.FactOTDProduction
					     where FinishedDate < DATEADD(day, DATEDIFF(day, 0, getdate()), 0) and DATEPART(WEEKDAY,FinishedDate) <> 7
					     ORDER BY FinishedDate desc)
		and FinishedDate < DATEADD(day, DATEDIFF(day, 0, getdate()), 0))

SELECT * from (
SELECT Production.ProductionID, Production.FinishedDate, case when DiffDaysToBackward > 0 THEN '-' else '+' end as BWS_ok, RessourceID, delta

from Production inner join Resource a on Production.ProductionID = a.ProductionID)Tab

PIVOT ( SUM(delta)
        For RessourceID
		in ([200011],[200012],[200013],[200021],[200022],[200023],[200024],[200031],[200041],[200042],[200051])) as pvt

barnabeck
Posting Yak Master

236 Posts

Posted - 2015-02-24 : 10:16:43

The problem was due to an indicator in my query that marks '+' and '-' for fulfillment/non-fulfillment. For some reason it can't be processed as a string. I changed:
case when DiffDaysToBackward > 0 THEN '-' else '+' end as BWS_ok
to
case when DiffDaysToBackward > 0 THEN 0 else 1 end as BWS_ok

and everything is fine.

Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources