Creating a serialized array - 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

Creating a serialized array

Author

Topic

ConradK
Posting Yak Master

140 Posts

Posted - 2010-05-05 : 15:20:57

If you can even call it that.

Okay, I have a table

item1 0
item2 3
item4 2

and I want to create a select statement that exports as

item2
item2
item2
item4
item4

see what I dun there?

bonus points if you can serialize them as they go, so it looks like

item2-1
item2-2
item2-3
item4-1
item4-2

see whats happeneing? We currently do this with a bunch of vb macros and excel sheets, but I'm hoping to port as much of the process into sql as I can. Any ideas or leads?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-05 : 16:08:27

Assuming the column names are Item, Count:

DECLARE @Max INT

SELECT @Max = MAX([Count])
FROM tableName

SELECT Item + '-' + CAST(ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Item) AS VARCHAR) 
FROM tableName t
INNER JOIN (
		SELECT number + 1 AS number
		FROM master.dbo.spt_values
		WHERE number < @Max
			AND type = 'p' ) n
	ON n.number <= t.[Count]

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

ConradK
Posting Yak Master

140 Posts

Posted - 2010-05-05 : 16:27:07

You sir, are a genius.

I think. Still gotta spot check the results...

but... wow....

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-05 : 16:34:30

The Maximum value for number in master.dbo.spt_values is 2047, so it will work with maximum count value of 2048.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

ConradK
Posting Yak Master

140 Posts

Posted - 2010-05-05 : 16:41:13

oh yeah, that should do fine. Once our company is big enough for a count value there over 2048 they won't need my code anymore anyways ;-)

YOU ARE AWESOME!!!

ConradK
Posting Yak Master

140 Posts

Posted - 2010-05-05 : 16:53:18

Okay, similar problem, lets see if you can do this one. Now I have another table with priorities, and I would like it serialized as well.

Example data

ID, Item, Priority

123, item1, 4
012, item2, 1
456, item1, 9
345, item2, 6
789, item1, 2

result should be
789, item1-1, 2
123, item1-2, 4
456, item1-3, 9
012, item2-1, 1
345, item2-2, 6

I don't think this one will be possible. We'll prolly just have to use the system we have now for this one, but if you have an answer at all, it would finish the project rather cleanly.

ConradK
Posting Yak Master

140 Posts

Posted - 2010-05-05 : 16:57:06

wait, would it jsut be a change in 'order by t.websku' to 'order by t.websku, t.priority' ?!?!?!? WILL TRY!!!!!

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-05 : 17:31:27

quote:
Originally posted by ConradK

wait, would it jsut be a change in 'order by t.websku' to 'order by t.websku, t.priority' ?!?!?!? WILL TRY!!!!!

AND...
use a ROW_NUMER() OVER (PARTITION BY Item ORDER BY Priority) to give you the suffixes.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2010-05-06 : 02:03:29

with cte as(
select item, serial, 1 rn
from tablename
union all
select t.item, t.serial, rn+1 rn
from tablename t join cte c on t.item=c.item and c.rn<t.serial)
select item, serial from cte order by item, serial

Hope can help...but advise to wait pros with confirmation...

ConradK
Posting Yak Master

140 Posts

Posted - 2010-05-06 : 10:42:19

DECLARE @Max INT

SELECT @Max = MAX(iasp.counter)
FROM iasp

SELECT

iasp.itemid
,iasp.productname + '-' + CAST(ROW_NUMBER() OVER (PARTITION BY iasp.productname ORDER BY iasp.productname, iasp.priority desc) AS VARCHAR) as "SSKU"
,iasp.priority
FROM iasp
INNER JOIN (
SELECT number + 1 AS number
FROM master.dbo.spt_values
WHERE number < @Max
AND type = 'p' ) n
ON n.number <= iasp.counter
order by ssku

Its giving me the itemid multiple times. My first infintile reaction was to use 'distinct', that just made it more crazyness.... whats going on here?

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-06 : 10:55:58

For the second request, does this not give you what you want?

SELECT
iasp.itemid
,iasp.productname + '-' + CAST(ROW_NUMBER() OVER (PARTITION BY iasp.productname ORDER BY iasp.productname, iasp.priority desc) AS VARCHAR) as "SSKU"
,iasp.priority
FROM iasp
order by ssku

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

ConradK
Posting Yak Master

140 Posts

Posted - 2010-05-06 : 11:00:03

yes... god yes... so I was over thinking it by inner joining that number table... I've been doing SQl for about 4 months here, self-taught and dragged along by your guys' genius, and this is just a tad over my head.... I think I see what you did there though...

Thank you ever so much....

yeah, for some reason with the code I was using before it was squaring the counter number! so I would have a max of 6 and get 36 serialized, with multiples in the itemid....

ConradK
Posting Yak Master

140 Posts

Posted - 2010-05-06 : 11:02:37

In other words, YOU ARE AWESOME!!!!!!!!

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-06 : 11:14:32

Glad I could help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources