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.
Go to Top of Page

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
Go to Top of Page

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 ...
Go to Top of Page

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...
Go to Top of Page

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
Go to Top of Page

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 ....
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -