May 27, 2009 at 12:45 pm
I checked the BOL too, it didn't help me with my current situation and it did help me understand a little better.
May 27, 2009 at 1:22 pm
Hi Michelle,
There's no guarantee that I can help, but if you post some sample data, the code that you are working on and the output you are wanting, I can take a look.
(Follow the link in Jeff's signature if you need any help on how to post data)
Someone'll be able to help if you can post this information!
Allister
May 27, 2009 at 2:03 pm
mmunson (5/27/2009)
Aw heck, I'm in over my head! :crying: I don't know what to do.Jeff, your script work perfect, and I was basically able to understand it. So I tried to incorporate it into my script and it's not working out so good. I tried creating a CTE with this one, but I'm kinda lost.
I think I need some help again! I could kick myself in the butt because you basically gave me the answer and I still can't figure it out.
Michelle :unsure:
I don't know if you tried it or not, but use the ol' "Divide'n'Conquer" method.... put the output of your complicated query into a Temp Table and then wittle on that... it'll make your life a lot easier than trying to do it all in one query.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 27, 2009 at 3:09 pm
Hmm Mmm, I tried that and read your comments very carefully. But when you re-iterated it again it started making a little more sense. I think I'm really close this time.
This is what I've done so far:
--===== Solution starts here
--===== Conditionally drop the work table
IF OBJECT_ID('Tempdb..#NormalNVP') IS NOT NULL
DROP TABLE #NormalNVP
--===== Declare the dynamic SQL Variables
DECLARE @SQLSelect VARCHAR(8000),
@SQLSelectList VARCHAR(8000),
@SQLFrom VARCHAR(8000)
--===== Split the RoleDesc and save the data in a temp table because
-- we're going to use it more than once. We could use the a
-- CTE twice, but the code for it would be executed twice and
-- it would simply make life a bit more difficult. Temp table
-- is a lot easier here because it allows for very simple
-- "Divide'n'Conquer" programming... might be faster, too.
SELECT DISTINCT
--=====I'm selecting the rest of the columns I want displayed here=====--
CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]
, CRM_PartsLabor.ORDERED_PROD as [Part No]
, CRM_PartsLabor.DESCRIPTION as [Part Desc]
, CRM_Confirmations.POSTING_DATE as [Date Consumed]
, CRM_StatusCodes.USER_STATUS as [Part Status]
, CRM_PartsLabor.QUANTITY as [Quantity]
, CRM_StatusCodes.END_DATE as [Service Order Last Change]
, CRM_Orders.SERIAL as [Serial No]
, CRM_Partners.DESCRIPTION + ', ' + CRM_Partners.ADDRESS as [Role]
, CRM_Orders.PROCESS_TYPE
INTO #NormalNVP
FROM (--==== Split the RoleDesc column like it should have been split in the
-- original NVP (Name/Value Pair) table.
SELECT PartNo,
SUBSTRING(RoleDesc,1,CHARINDEX(',',RoleDesc)-1) AS NVPRole,
SUBSTRING(RoleDesc,CHARINDEX(',',RoleDesc)+2,8000) NVPValue
FROM #PivotExample
) d,
--=====These are my joins, although I'm not sure if I have to delete the #PivotExample=====--
CRM_PartsLabor INNER JOIN CRM_Orders ON
CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_Confirmations ON
CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
--=====Here are my conditional clauses=====--
WHERE
CRM_PartsLabor.TRANSACTION_ID like ('3%')
and CRM_StatusCodes.user_STATUS = 'Complete'
and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN','BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR','CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK','SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
--===== Create the static part of the SELECT
SELECT @SQLSelect = ' SELECT PartNo,' + CHAR(10)
--===== Create the dynamic SELECT list
SELECT @SQLSelectList = ISNULL(@SQLSelectList + ',' + CHAR(10),'') + SPACE(8)
+ 'MAX(CASE WHEN NVPRole = ' + QUOTENAME(NVPRole,'''')
+ ' THEN NVPValue END) AS ' + QUOTENAME(NVPRole)
FROM #NormalNVP
GROUP BY NVPRole
--===== Create the static FROM clause
SELECT @SQLFrom = '
FROM #NormalNVP
GROUP BY PartNo
ORDER BY PartNo
'
--===== Display the Dynmamic SQL we just created
PRINT @SQLSelect + @SQLSelectList + @SQLFrom
--===== Execute the Dynamic SQL to solve the problem
EXEC (@SQLSelect + @SQLSelectList + @SQLFrom)
This is the error I get:
Msg 207, Level 16, State 1, Line 65
Invalid column name 'NVPRole'.
Msg 207, Level 16, State 1, Line 62
Invalid column name 'NVPRole'.
Msg 207, Level 16, State 1, Line 63
Invalid column name 'NVPRole'.
What do you think of my progress so far? Am I on the right track?
Thank you!!!
Michelle
May 27, 2009 at 3:57 pm
Hi Michelle, the #PivotTable was created from the sample output from your first post.
The code you have posted in your last doesn't use this table, I'm guessing you want to use the data from your select distinct statement.
Also the #NormalNVP table was created to split the concatenated [Role] string created by you in the select distinct statement.
Can I suggest you use the following select statement to create #NormalNVP?
SELECT DISTINCT
CRM_PartsLabor.ORDERED_PROD as [Part No]
, CRM_Partners.DESCRIPTION as NVPRole
, CRM_Partners.ADDRESS as NVPValue
INTO #NormalNVP
FROM
CRM_PartsLabor INNER JOIN CRM_Orders ON
CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_Confirmations ON
CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE
CRM_PartsLabor.TRANSACTION_ID like ('3%')
and CRM_StatusCodes.user_STATUS = 'Complete'
and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN',
'BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR',
'CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK',
'SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
Additionally, I'm not sure without seeing the data, but it looks like you know up front which CRM_Partners you are wanting in the output; the point of the dynamic sql is to create columns when you don't know what role columns you want in the output.
May 29, 2009 at 9:30 am
Thanks Allister, what you said made sense! And is now obvious to me, I think I was sitting in front of the screen way too long! :doze:
But I'm back at it again... still trying to make it work. My boss isn't real happy now because I can't get it into the format he wants and he wants IT to change the architecture of the db. And he got somebody from IT to help me but he looked at the problem and just told me, 'Yeah, good luck with that'! Not real encouraging. So I'm here to get advice from the real experts. 😉
Here's my new script:
--===== Solution starts here
--===== Conditionally drop the work table
IF OBJECT_ID('Tempdb..#NormalNVP') IS NOT NULL
DROP TABLE #NormalNVP
--===== Declare the dynamic SQL Variables
DECLARE @SQLSelect VARCHAR(8000),
@SQLSelectList VARCHAR(8000),
@SQLFrom VARCHAR(8000)
--===== Split the RoleDesc and save the data in a temp table because
-- we're going to use it more than once. We could use the a
-- CTE twice, but the code for it would be executed twice and
-- it would simply make life a bit more difficult. Temp table
-- is a lot easier here because it allows for very simple
-- "Divide'n'Conquer" programming... might be faster, too.
SELECT DISTINCT
--=====I'm selecting the rest of the columns I want displayed here=====--
CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]
, CRM_PartsLabor.ORDERED_PROD as [Part No]
, CRM_PartsLabor.DESCRIPTION as [Part Desc]
, CRM_Confirmations.POSTING_DATE as [Date Consumed]
, CRM_StatusCodes.USER_STATUS as [Part Status]
, CRM_PartsLabor.QUANTITY as [Quantity]
, CRM_StatusCodes.END_DATE as [Service Order Last Change]
, CRM_Orders.SERIAL as [Serial No]
--, CRM_Partners.DESCRIPTION + ', ' + CRM_Partners.ADDRESS as [Role]
, CRM_Orders.PROCESS_TYPE
--=====goes into temp tabel here=====--
INTO #NormalNVP
FROM (--==== Split the RoleDesc column like it should have been split in the
-- original NVP (Name/Value Pair) table.
SELECT PartNo,
SUBSTRING(RoleDesc,1,CHARINDEX(',',RoleDesc)-1) AS NVPRole,
SUBSTRING(RoleDesc,CHARINDEX(',',RoleDesc)+2,8000) NVPValue
FROM #NormalNVP
) d,
--=====These are my joins=====--
CRM_PartsLabor INNER JOIN CRM_Orders ON
CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_Confirmations ON
CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
--=====Here are my conditional clauses=====--
WHERE
CRM_PartsLabor.TRANSACTION_ID like ('3%')
and CRM_StatusCodes.user_STATUS = 'Complete'
and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN', 'BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR', 'CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK','SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
--===== Create the static part of the SELECT
SELECT @SQLSelect = ' SELECT PartNo,' + CHAR(10)
--===== Create the dynamic SELECT list
SELECT @SQLSelectList = ISNULL(@SQLSelectList + ',' + CHAR(10),'') + SPACE(8)
+ 'MAX(CASE WHEN NVPRole = ' + QUOTENAME(NVPRole,'''')
+ ' THEN NVPValue END) AS ' + QUOTENAME(NVPRole)
FROM #NormalNVP
GROUP BY NVPRole
--===== Create the static FROM clause
SELECT @SQLFrom = '
FROM #NormalNVP
GROUP BY PartNo
ORDER BY PartNo
'
--===== Display the Dynmamic SQL we just created
PRINT @SQLSelect + @SQLSelectList + @SQLFrom
--===== Execute the Dynamic SQL to solve the problem
EXEC (@SQLSelect + @SQLSelectList + @SQLFrom)
think my problem might be with the 'INTO #NormalNVP', but I don't know what temp table to dump it into?
This is my result set from my original query:
SerOrdID PartNoPartDesc Date Consumed Status Qty SerLastChg SerialNo Role PRo type
36 102031 Upgrade Windows Vista20090315 Complete 1 3/15/09 139 Emp, Jeff A ZSVO
36 102031 Upgrade Windows Vista20090315 Complete 1 3/15/09 139 Emp Center ZSVO
36 102031 Upgrade Windows Vista20090315 Complete 1 3/15/09 139 Sold Med Ctr ZSVO
36 102503 Software Upgrade, 2.2.4220090315 Complete 1 3/15/09 139 ExecEmp Jeff A ZSVO
36 102503 Kit, Software Upgrade 3.120090315 Complete 1 3/15/09 139 ServEmp Ctr ZSVO
36 102503 Kit, Software Upgrade, 2.220090315 Complete 1 3/15/09 139 Sold Med Ctr ZSVO
(I edited the content a bit to try to make it look resonably presentable)
In my result set I have lines which are duplicate except when it comes to the Role column. In the example above where it says for the PartDesc, Windows Vista the Role has three different entries. My goal is to transpose the rows for the Role column into three columns so everything is on one line.
Jeff helped me big time and gave me the solution here:
--===== Solution starts here
--===== Conditionally drop the work table
IF OBJECT_ID('Tempdb..#NormalNVP') IS NOT NULL
DROP TABLE #NormalNVP
--===== Declare the dynamic SQL Variables
DECLARE @SQLSelect VARCHAR(8000),
@SQLSelectList VARCHAR(8000),
@SQLFrom VARCHAR(8000)
--===== Split the RoleDesc and save the data in a temp table because
-- we're going to use it more than once. We could use the a
-- CTE twice, but the code for it would be executed twice and
-- it would simply make life a bit more difficult. Temp table
-- is a lot easier here because it allows for very simple
-- "Divide'n'Conquer" programming... might be faster, too.
SELECT *
INTO #NormalNVP
FROM (--==== Split the RoleDesc column like it should have been split in the
-- original NVP (Name/Value Pair) table.
SELECT PartNo,
SUBSTRING(RoleDesc,1,CHARINDEX(',',RoleDesc)-1) AS NVPRole,
SUBSTRING(RoleDesc,CHARINDEX(',',RoleDesc)+2,8000) NVPValue
FROM #PivotExample
) d
--===== Create the static part of the SELECT
SELECT @SQLSelect = ' SELECT PartNo,' + CHAR(10)
--===== Create the dynamic SELECT list
SELECT @SQLSelectList = ISNULL(@SQLSelectList + ',' + CHAR(10),'') + SPACE(8)
+ 'MAX(CASE WHEN NVPRole = ' + QUOTENAME(NVPRole,'''')
+ ' THEN NVPValue END) AS ' + QUOTENAME(NVPRole)
FROM #NormalNVP
GROUP BY NVPRole
--===== Create the static FROM clause
SELECT @SQLFrom = '
FROM #NormalNVP
GROUP BY PartNo
ORDER BY PartNo
'
--===== Display the Dynmamic SQL we just created
PRINT @SQLSelect + @SQLSelectList + @SQLFrom
--===== Execute the Dynamic SQL to solve the problem
EXEC (@SQLSelect + @SQLSelectList + @SQLFrom)
And this is the result set:
PartNoExec. Service EmployeeService Employee Group Sold-To Party
102031Jeff A. Global Call Center McLaren Center
102503Jeff A. Global Call Center Center
5-903-6 Jeff A. Global Call Center Center
This is absolutely perfect! This is what I want. But I'm struggling with trying to make this work with my query. Which is what I put at the beginning of the post. I think I'm almost there but I error I get is:
Msg 208, Level 16, State 0, Line 17
Invalid object name '#NormalNVP'.
If I can get get this to work, I can re-use this with all my scripts.
This is so difficult for me, and my boss is looking at me now like I'm a complete idiot (mind you, he's not an IT person either, and I am). And now he's running around the IT department trying to fnd somebody who can fix this so he can get what he wants, but nobody knows how to do it.
I'm desperate. A million thanks you to anybody who can help me make this work.
Michelle :sick:
May 29, 2009 at 9:45 am
I had another idea, I think I was missing something from Jeff's solution.
This is the other script I came up with but I still get an error.
--===== Conditionally drop, then recreate and populate the test table.
-- None of this is part of the solution. It just provides a
-- test bed for the coded solution that follows this section.
IF OBJECT_ID('TempDB..#PivotExample') IS NOT NULL
DROP TABLE #PivotExample
CREATE TABLE #PivotExample (PartNo VARCHAR(12),RoleDesc VARCHAR(64))
INSERT INTO #PivotExample
SELECT CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]UNION ALL
SELECT CRM_PartsLabor.ORDERED_PROD as [Part No]UNION ALL
SELECT CRM_PartsLabor.DESCRIPTION as [Part Desc]UNION ALL
SELECT CRM_Confirmations.POSTING_DATE as [Date Consumed]UNION ALL
SELECT CRM_StatusCodes.USER_STATUS as [Part Status]UNION ALL
SELECT CRM_PartsLabor.QUANTITY as [Quantity]UNION ALL
SELECT CRM_StatusCodes.END_DATE as [Service Order Last Change]UNION ALL
SELECT CRM_Orders.SERIAL as [Serial No]UNION ALL
SELECT CRM_Partners.DESCRIPTION + ', ' + CRM_Partners.ADDRESS as [Role]UNION ALL
SELECT CRM_Orders.PROCESS_TYPEUNION ALL
FROM
CRM_PartsLabor INNER JOIN CRM_Orders ON
CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_Confirmations ON
CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE
CRM_PartsLabor.TRANSACTION_ID like ('3%')
and CRM_StatusCodes.user_STATUS = 'Complete'
and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN', 'BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR', 'CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK','SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
--===== Solution starts here
--===== Conditionally drop the work table
IF OBJECT_ID('Tempdb..#NormalNVP') IS NOT NULL
DROP TABLE #NormalNVP
--===== Declare the dynamic SQL Variables
DECLARE @SQLSelect VARCHAR(8000),
@SQLSelectList VARCHAR(8000),
@SQLFrom VARCHAR(8000)
--===== Split the RoleDesc and save the data in a temp table because
-- we're going to use it more than once. We could use the a
-- CTE twice, but the code for it would be executed twice and
-- it would simply make life a bit more difficult. Temp table
-- is a lot easier here because it allows for very simple
-- "Divide'n'Conquer" programming... might be faster, too.
SELECT *
INTO #NormalNVP
FROM (--==== Split the RoleDesc column like it should have been split in the
-- original NVP (Name/Value Pair) table.
SELECT PartNo,
SUBSTRING(RoleDesc,1,CHARINDEX(',',RoleDesc)-1) AS NVPRole,
SUBSTRING(RoleDesc,CHARINDEX(',',RoleDesc)+2,8000) NVPValue
FROM #PivotExample
) d
--===== Create the static part of the SELECT
SELECT @SQLSelect = ' SELECT PartNo,' + CHAR(10)
--===== Create the dynamic SELECT list
SELECT @SQLSelectList = ISNULL(@SQLSelectList + ',' + CHAR(10),'') + SPACE(8)
+ 'MAX(CASE WHEN NVPRole = ' + QUOTENAME(NVPRole,'''')
+ ' THEN NVPValue END) AS ' + QUOTENAME(NVPRole)
FROM #NormalNVP
GROUP BY NVPRole
--===== Create the static FROM clause
SELECT @SQLFrom = '
FROM #NormalNVP
GROUP BY PartNo
ORDER BY PartNo
'
--===== Display the Dynmamic SQL we just created
PRINT @SQLSelect + @SQLSelectList + @SQLFrom
--===== Execute the Dynamic SQL to solve the problem
EXEC (@SQLSelect + @SQLSelectList + @SQLFrom)
Thank you!! ~Michelle
May 29, 2009 at 10:23 am
Michelle,
I’m really not clear on what you are trying to achieve — what output are you expecting/wanting?
Can you tell me if the crm partners are known ahead of time? I am assuming this due to “CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')”
If this is the case then you really don’t need Jeff’s dynamic cross-tab sql.
As far as the error you are getting, the following is, as far as I can tell, totally syntactically incorrect:
IF OBJECT_ID('TempDB..#PivotExample') IS NOT NULL
DROP TABLE #PivotExample
CREATE TABLE #PivotExample (PartNo VARCHAR(12),RoleDesc VARCHAR(64))
INSERT INTO #PivotExample
SELECT CRM_PartsLabor.TRANSACTION_ID as [Service Order ID] UNION ALL
SELECT CRM_PartsLabor.ORDERED_PROD as [Part No] UNION ALL
SELECT CRM_PartsLabor.DESCRIPTION as [Part Desc] UNION ALL
SELECT CRM_Confirmations.POSTING_DATE as [Date Consumed] UNION ALL
SELECT CRM_StatusCodes.USER_STATUS as [Part Status] UNION ALL
SELECT CRM_PartsLabor.QUANTITY as [Quantity] UNION ALL
SELECT CRM_StatusCodes.END_DATE as [Service Order Last Change] UNION ALL
SELECT CRM_Orders.SERIAL as [Serial No] UNION ALL
SELECT CRM_Partners.DESCRIPTION + ', ' + CRM_Partners.ADDRESS as [Role] UNION ALL
SELECT CRM_Orders.PROCESS_TYPE UNION ALL
FROM
CRM_PartsLabor INNER JOIN CRM_Orders ON
CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_Confirmations ON
CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE
CRM_PartsLabor.TRANSACTION_ID like ('3%')
and CRM_StatusCodes.user_STATUS = 'Complete'
and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN', 'BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR', 'CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK','SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
I think you have tied yourself in knots, and I feel for you, but if you are unclear about what you are after then it’s gonna be impossible for anyone to help.
All I can suggest at this point is for you to re-post the output as you want it and we take it from there.
Allister
May 29, 2009 at 10:44 am
Hi Allister, thanks for your advice!
Yes, the CRM_Partners is known ahead of time.
The error I get with the query is:
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'FROM'.
My original query is:
use servicedw
--======Completed Confs parts & Qty=====--
SELECT DISTINCT
CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]
, CRM_PartsLabor.ORDERED_PROD as [Part No]
, CRM_PartsLabor.DESCRIPTION as [Part Desc]
, CRM_Confirmations.POSTING_DATE as [Date Consumed]
, CRM_StatusCodes.USER_STATUS as [Part Status]
, CRM_PartsLabor.QUANTITY as [Quantity]
, CRM_StatusCodes.END_DATE as [Service Order Last Change]
, CRM_Orders.SERIAL as [Serial No]
, CRM_Partners.DESCRIPTION + ', ' + CRM_Partners.ADDRESS as [Role]
, CRM_Orders.PROCESS_TYPE
FROM
CRM_PartsLabor INNER JOIN CRM_Orders ON
CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_Confirmations ON
CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE
CRM_PartsLabor.TRANSACTION_ID like ('3%')
and CRM_StatusCodes.user_STATUS = 'Complete'
and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN',
'BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR',
'CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK',
'SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
...and my output is:
Service Order IDPart NoPart DescDate ConsumedPart StatusQuantityService Order Last ChangeSerial NoRolePROCESS_TYPE
3000000006102031Upgrade, License, Windows Vista20090315Complete13/15/09 5:23 PM110039Exec. Service Employee, Jeff A.ZSVO
3000000006102031Upgrade, License, Windows Vista20090315Complete13/15/09 5:23 PM110039Service Employee Group, CRM_CC CRM Global Call CenterZSVO
3000000006102031Upgrade, License, Windows Vista20090315Complete13/15/09 5:23 PM110039Sold-To Party, Regional Medical CenterZSVO
In my example output I have three lines that are identical except for the 'Role'. I want to transpose the 'Role' into columns. So it looks like this:
Exec. Service EmployeeService Employee GroupSold-To Party
Jeff A. Global Call Center McLaren Center
...and not like this
Role
Jeff A.
Glocal Call Center
McLaren Center
...in the end I want it to looks like this
Service Order IDPart NoPart DescDate ConsumedPart Status QuantityService Order Last Change
3000000006102031UpgradeWindows Vista 20090315Complete 1 3/15/09 5:23 PM
Serial No Exec. Service EmployeeService Employee GroupSold-To Party
110039 Jeff A. Global Call Center McLaren Center
...so I have a 1 line and not three.
I hope this makes little more sense and thanks for sticking with me Allister to help me resolve this. If I can get this one thing figured out today this will be a big win for me!
~Michelle
May 29, 2009 at 10:56 am
OK, you are concatenating the two fields that you need separate for the required output: CRM_Partners.DESCRIPTION + ', ' + CRM_Partners.ADDRESS as [Role]... these need to be in two columns in the intermediate table for you to have any chance of success. Did you try executing this query?:
SELECT DISTINCT
CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]
, CRM_PartsLabor.ORDERED_PROD as [Part No]
, CRM_PartsLabor.DESCRIPTION as [Part Desc]
, CRM_Confirmations.POSTING_DATE as [Date Consumed]
, CRM_StatusCodes.USER_STATUS as [Part Status]
, CRM_PartsLabor.QUANTITY as [Quantity]
, CRM_StatusCodes.END_DATE as [Service Order Last Change]
, CRM_Orders.SERIAL as [Serial No], CRM_Orders.PROCESS_TYPE
, CRM_Partners.ADDRESS as NVPValue
INTO #NormalNVP
FROM
CRM_PartsLabor INNER JOIN CRM_Orders ON
CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_Confirmations ON
CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE
CRM_PartsLabor.TRANSACTION_ID like ('3%')
and CRM_StatusCodes.user_STATUS = 'Complete'
and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN',
'BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR',
'CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK',
'SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
May 29, 2009 at 10:59 am
Sorry, got columns outta order:
CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]
, CRM_PartsLabor.ORDERED_PROD as [Part No]
, CRM_PartsLabor.DESCRIPTION as [Part Desc]
, CRM_Confirmations.POSTING_DATE as [Date Consumed]
, CRM_StatusCodes.USER_STATUS as [Part Status]
, CRM_PartsLabor.QUANTITY as [Quantity]
, CRM_StatusCodes.END_DATE as [Service Order Last Change]
, CRM_Orders.SERIAL as [Serial No], CRM_Orders.PROCESS_TYPE
, CRM_Partners.DESCRIPTION as NVPRole
, CRM_Partners.ADDRESS as NVPValue
INTO #NormalNVP
FROM
CRM_PartsLabor INNER JOIN CRM_Orders ON
CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_Confirmations ON
CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE
CRM_PartsLabor.TRANSACTION_ID like ('3%')
and CRM_StatusCodes.user_STATUS = 'Complete'
and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN',
'BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR',
'CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK',
'SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
May 29, 2009 at 11:05 am
Yes, I tried it. But it doesn't give me an output, it just says, '(15333 row(s) affected)'. I know getting an output be very simple, but I'm feeling kinda' dumb right now.
It must be because of the INTO statement....
Michelle
May 29, 2009 at 11:08 am
yeah, it's putting the result into a temporary table, have a look at the following:
select
CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]
, CRM_PartsLabor.ORDERED_PROD as [Part No]
, CRM_PartsLabor.DESCRIPTION as [Part Desc]
, CRM_Confirmations.POSTING_DATE as [Date Consumed]
, CRM_StatusCodes.USER_STATUS as [Part Status]
, CRM_PartsLabor.QUANTITY as [Quantity]
, CRM_StatusCodes.END_DATE as [Service Order Last Change]
, CRM_Orders.SERIAL as [Serial No], CRM_Orders.PROCESS_TYPE
, CRM_Partners.DESCRIPTION as NVPRole
, CRM_Partners.ADDRESS as NVPValue
INTO #NormalNVP
FROM
CRM_PartsLabor INNER JOIN CRM_Orders ON
CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_Confirmations ON
CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE
CRM_PartsLabor.TRANSACTION_ID like ('3%')
and CRM_StatusCodes.user_STATUS = 'Complete'
and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN',
'BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR',
'CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK',
'SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
-- Here is the contents of the table
select * from #NormalNVP
May 29, 2009 at 11:58 am
Ah yes, of course!
I gave it a try and here are my results:
Service Order ID Part No Part Desc Date Consumed Part Status Quantity Service Order Last Change Serial No PROCESS_TYPE NVPRole NVPValue
3000000006 102503 Kit, Software Upgrade, 2.2.4 to 3.1.2 20090315 Complete 1 3/15/09 5:23 PM 110039 ZSVO Sold-To PartyMcLaren Regional
3000000006 102503 Kit, Software Upgrade, 2.2.4 to 3.1.2 20090315 Complete 1 3/15/09 5:23 PM 110039 ZSVO Service Employee Group CRM_CC CRM Global Call Center
3000000006 102503 Kit, Software Upgrade, 2.2.4 to 3.1.2 20090315 Complete 1 3/15/09 5:23 PM 110039 ZSVO Exec. Service Employee Jeff Smith
I'm still getting the same results.
I'm thinking we need to stick with either a cross tab or pivot query.
I started messing around with one a bit.
--===== sample pivot with complete confs & Qty =====--
use servicedw
--======Completed Confs parts & Qty=====--
SELECT
CRM_Partners.DESCRIPTION, [1] AS [Desc], [2] AS [Desc2], [3] AS [Desc3],
CRM_Partners.ADDRESS, [1] AS [Role]
FROM CRM_Partners
(SELECT
CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]
, CRM_PartsLabor.ORDERED_PROD as [Part No]
, CRM_PartsLabor.DESCRIPTION as [Part Desc]
, CRM_Confirmations.POSTING_DATE as [Date Consumed]
, CRM_StatusCodes.USER_STATUS as [Part Status]
, CRM_PartsLabor.QUANTITY as [Quantity]
, CRM_StatusCodes.END_DATE as [Service Order Last Change]
, CRM_Orders.SERIAL as [Serial No]
, CRM_Orders.PROCESS_TYPE
FROM
CRM_PartsLabor INNER JOIN CRM_Orders ON
CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_Confirmations ON
CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID) P
PIVOT
as PVT
WHERE
CRM_PartsLabor.TRANSACTION_ID like ('3%')
and CRM_StatusCodes.user_STATUS = 'Complete'
and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN',
'BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR',
'CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK',
'SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
Thanks again for your time, Allister.
~Michelle 🙂
May 29, 2009 at 12:05 pm
Ok, then putting it all together:
select
CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]
, CRM_PartsLabor.ORDERED_PROD as [Part No]
, CRM_PartsLabor.DESCRIPTION as [Part Desc]
, CRM_Confirmations.POSTING_DATE as [Date Consumed]
, CRM_StatusCodes.USER_STATUS as [Part Status]
, CRM_PartsLabor.QUANTITY as [Quantity]
, CRM_StatusCodes.END_DATE as [Service Order Last Change]
, CRM_Orders.SERIAL as [Serial No], CRM_Orders.PROCESS_TYPE
, CRM_Partners.DESCRIPTION as NVPRole
, CRM_Partners.ADDRESS as NVPValue
INTO #NormalNVP
FROM
CRM_PartsLabor INNER JOIN CRM_Orders ON
CRM_PartsLabor.TRANSACTION_ID = CRM_Orders.TRANSACTION_ID
INNER JOIN CRM_StatusCodes ON CRM_Orders.TRANSACTION_ID = CRM_StatusCodes.TRANSACTION_ID
INNER JOIN CRM_Confirmations ON
CRM_StatusCodes.TRANSACTION_ID = CRM_Confirmations.TRANSACTION_ID
INNER JOIN CRM_Partners ON CRM_Orders.PARTNER_ID = CRM_Partners.PARTNER_ID
WHERE
CRM_PartsLabor.TRANSACTION_ID like ('3%')
and CRM_StatusCodes.user_STATUS = 'Complete'
and CRM_PartsLabor.ORDERED_PROD NOT IN ('AI','AP','BEAM0030_SVC_PLAN','BEAM0090_SVC_PLAN',
'BEAM0150_SVC_PLAN','BEAM0300_SVC_PLAN','BEAM0600_SVC_PLAN','BEAM1200_SVC_PLAN','CALLCENTER_LABOR',
'CLINICALAPPS_LABOR','CLINICALAPPS_TASK','DATA','DIST_SLF_APAC','DIST_TLC_APAC',
'FSE_LABOR','H-5007-0000','H-5008-0000','INSTALL_TASK_LIST','INSTALLATION_TASK',
'ISP_TASK','KB','MEDPHYSICS_LABOR','MEDPHYSICS_TASK','PARTNERSHIP_TLC','PROJECT_MNGT_TASK',
'SERVICE LABOR','TOTAL_TLC_EMEA','TOTAL_TLC_NA','WARRANTY_APAC','WARRANTY_DIST_APAC',
'WARRANTY_DIST_EMEA','WARRANTY_EMEA','WARRANTY_NA')
and CRM_Partners.PARTNER_FCT IN ('00000001','00000056','00000052')
-- Here is the contents of the table
select * from #NormalNVP
--===== Create the static part of the SELECT
SELECT @SQLSelect = ' SELECT [Service Order ID],
[Part No], [Part Desc], Date Consumed], [Date Consumed], [Part Status], [Quantity], [Service Order Last Change], [Serial No] , [PROCESS_TYPE], ' + CHAR(10)
--===== Create the dynamic SELECT list
SELECT @SQLSelectList = ISNULL(@SQLSelectList + ',' + CHAR(10),'') + SPACE(8)
+ 'MAX(CASE WHEN NVPRole = ' + QUOTENAME(NVPRole,'''')
+ ' THEN NVPValue END) AS ' + QUOTENAME(NVPRole)
FROM #NormalNVP
GROUP BY NVPRole
--===== Create the static FROM clause
SELECT @SQLFrom = '
FROM #NormalNVP
GROUP BY PartNo
ORDER BY PartNo
'
--===== Display the Dynmamic SQL we just created
PRINT @SQLSelect + @SQLSelectList + @SQLFrom
--===== Execute the Dynamic SQL to solve the problem
EXEC (@SQLSelect + @SQLSelectList + @SQLFrom)
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply