Cursor needed, I think... - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Cursor needed, I think...

Author

Topic

spunkiegirl
Starting Member

9 Posts

Posted - 2014-03-29 : 09:31:02

Good Morning,
I have a temp table named #T that has to have up to 6 parts inserted into the table. Part1, Part2, Part3, Part4, Part5 and Part6. I need to query a table named Parts that has a mixture of parts and labor. I need to insert all the part numbers into my temp table. I need to check to see if the line item type is "P", if it is, I need the Part Number and Description inserted into the temp table. I was thinking I needed a cursor, but I have not written one of those in years. Please help!
Thank you,
SpunkieGirl

Robowski
Posting Yak Master

101 Posts

Posted - 2014-03-31 : 08:34:26

provide some table layouts, sample data and expected example result and it will be easier to help :)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-31 : 13:17:08

I'm virtually certain you won't need a cursor. Here's the general outline of what you're doing, but need more details to go any further.



INSERT INTO #T ( ... )
SELECT ...
FROM ...
WHERE line_item_type = 'P'

spunkiegirl
Starting Member

9 Posts

Posted - 2014-03-31 : 15:43:13

I have a table called CallParts, it has a unique identifier named callnumber, one callnumber can have multiple parts. So callnumber, has 6 parts, part1,part2,part3,part4,part5,part6, each a separate row in the Parts table. I need to enter each one of these parts into a temptable, called #T.

The part table looks like below:

RowID CallNbr PartNbr
Row1 1234 4543
Row2 1234 3444
Row3 1234 2343
Row4 1234 2335
Row5 1234 2342
Row5 1234 2341

So my result in #T needs to look like the below.
CallNbr Part1 Part2 Part3 Part4 Part5 Part6
1234 4543 3444 2343 2335 2342 2341

Thank you!

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-03-31 : 17:31:50

SpunkieGirl, look at this...


DECLARE @Parts TABLE
(RowNbr INT, CallNbr INT, PartNbr INT)

INSERT INTO @Parts VALUES(1, 1234, 4543), (2, 1234, 3444), (3, 1234, 2343), (4, 1234, 2335), (5, 1234, 2342), (6, 1234, 2341)

SELECT pvt.CallNbr
	, pvt.[1] AS Part1, pvt.[2] AS Part2, pvt.[3] AS Part3, pvt.[4] AS Part4, pvt.[5] AS Part5, pvt.[6] AS Part6
FROM (SELECT RowNbr, CallNbr, PartNbr FROM @Parts)a
PIVOT(MAX(PartNbr) FOR RowNbr IN ([1], [2],[3],[4],[5],[6]))pvt

Read more about Pivot in SQL Server @ http://sqlsaga.com/sql-server/how-to-use-pivot-to-transform-rows-into-columns-in-sql-server/

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-31 : 18:40:30

[code]
DECLARE @Parts TABLE
(RowID INT, CallNbr INT, PartNbr INT)

INSERT INTO @Parts VALUES(12, 1234, 4543), (14, 1234, 3444), (16, 1234, 2343), (18, 1234, 2335), (20, 1234, 2342), (22, 1234, 2341)
INSERT INTO @Parts VALUES(22, 9876, 4321), (24, 9876, 1234), (26, 9876, 4567), (28, 9876, 7654)

SELECT
pivot_table.CallNbr,
pivot_table.[1] AS part1, pivot_table.[2] AS part2, pivot_table.[3] AS part3,
pivot_table.[4] AS part4, pivot_table.[5] AS part5, pivot_table.[6] AS part6
FROM (
SELECT CallNbr, PartNbr, ROW_NUMBER() OVER(PARTITION BY CallNbr ORDER BY RowID) AS row_num
FROM @Parts
) AS base
PIVOT (
MAX(PartNbr) FOR row_num IN ( [1], [2], [3], [4], [5], [6] )
) AS pivot_table

[/code]

spunkiegirl
Starting Member

9 Posts

Posted - 2014-03-31 : 19:32:48

Ok, this is what I have so far...

select pvt.callnbr, pvt.[1] as Part1,pvt.[2] as Part2,pvt.[3] as Part3,pvt.[4] as Part4,pvt.[5] as Part5,pvt.[6] as Part6
from (select dex_row_id,callnbr,itemnmbr from SVC00203 where CALLNBR = '0001581546' and LINITMTYP = 'P')a
PIVOT(max(itemnmbr) for dex_row_id in ([1],[2],[3],[4],[5],[6])) pvt

But my result set does not show the parts, it is only showing NULL.
callnbr Part1 Part2 Part3 Part4 Part5 Part6
0001581546 NULL NULL NULL NULL NULL NULL

spunkiegirl
Starting Member

9 Posts

Posted - 2014-03-31 : 19:35:53

YEAH, it worked with the following...THANK YOU ALL SO MUCH for the help!!!

SELECT
pivot_table.CallNbr,
pivot_table.[1] AS part1, pivot_table.[2] AS part2, pivot_table.[3] AS part3,
pivot_table.[4] AS part4, pivot_table.[5] AS part5, pivot_table.[6] AS part6
FROM (
SELECT CallNbr, ItemNmbr, ROW_NUMBER() OVER(PARTITION BY CallNbr ORDER BY Dex_Row_ID) AS row_num
FROM svc00203 where CALLNBR = '0001581546' and LINITMTYP = 'P'
) AS base
PIVOT (
MAX(ItemNmbr) FOR row_num IN ( [1], [2], [3], [4], [5], [6] )
) AS pivot_table

spunkiegirl
Starting Member

9 Posts

Posted - 2014-03-31 : 23:14:52

Ok, now I want to add a description Pivot to the original query and for some reason, I cannot get it to work. Errggg!!!

I want to have 6 part numbers with 6 descriptions, right after each of the part number.

CallNbr Part1 PartDesc1 Part2 PartDesc2 Part3 PartDesc3, and so forth...

Below is the syntax I am using.

SELECT
pivot_table1.CallNbr,
pivot_table1.[1] AS Part1,
pivot_table2.[PartDesc1] AS PartDesc1,
pivot_table1.[2] AS part2,
pivot_table2.[PartDesc2] AS PartDesc2,
pivot_table1.[3] AS part3,
pivot_table2.[PartDesc3] AS PartDesc3,
pivot_table1.[4] AS part4,
pivot_table2.[PartDesc4] AS PartDesc4,
pivot_table1.[5] AS part5,
pivot_table2.[PartDesc5] AS PartDesc5,
pivot_table1.[6] AS part6,
pivot_table2.[PartDesc6] AS PartDesc6
into #Test2
FROM (
SELECT CallNbr, ItemNmbr,ROW_NUMBER() OVER(PARTITION BY CallNbr ORDER BY Dex_Row_ID) AS row_num
FROM bsv_svc00203 where CALLNBR = '0000001015' and LINITMTYP = 'P'
) AS base
PIVOT (
MAX(ItemNmbr) FOR row_num IN ( [1],[2],[3],[4],[5],[6] )
) AS pivot_table1
(
SELECT CallNbr, ItemDesc,ROW_NUMBER() OVER(PARTITION BY CallNbr ORDER BY Dex_Row_ID) AS row_num2
FROM bsv_svc00203 where CALLNBR = '0000001015' and LINITMTYP = 'P'
) AS base2
PIVOT (
MAX(ItemDesc) FOR row_num2 IN ([PartDesc1],[PartDesc2],[PartDesc3],[PartDesc4],[PartDesc5],[PartDesc6] )
) AS pivot_table2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources