Scope parameter: comma

Showing posts with label comma. Show all posts

Friday, March 23, 2012

Script to combine multiple rows into 1 single row

Hi,

I'm working on a system migration and I need to combine data from multiple
rows (with the same ID) into one comma separated string. This is how the
data is at the moment:

Company_ID Material
0x00C00000000053B86 Lead
0x00C00000000053B86 Sulphur
0x00C00000000053B86 Concrete

I need it in the following format:
Company_ID Material
0x00C00000000053B86 Lead, Sulphur, Concrete

There is no definite number of materials per Company.

I have read the part of
http://www.sommarskog.se/arrays-in-sql.html#iterative that talks about 'The
Iterative Method' but my knowledge of SQL is very limited and I don't know
how to use this code to get what I need.

Can anyone help me?Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

I'm working on a system migration and I need to combine data from multiple
rows (with the same ID) into one comma separated string. This is how the
data is at the moment:
>
Company_ID Material
0x00C00000000053B86 Lead
0x00C00000000053B86 Sulphur
0x00C00000000053B86 Concrete
>
I need it in the following format:
Company_ID Material
0x00C00000000053B86 Lead, Sulphur, Concrete
>
There is no definite number of materials per Company.
>
I have read the part of
http://www.sommarskog.se/arrays-in-sql.html#iterative that talks about
'The Iterative Method' but my knowledge of SQL is very limited and I
don't know how to use this code to get what I need.

And that article covers the opposite process - unpacking the list.

Composing the list is less funny, because it produces a result which
violates basic principles in a relational database: no repeating groups.
That is not to say that it's a stupid thing to ask for; it's not strange
to ask for this format in reporting. I get a little nervous when you
say that you are working with system migraton, because that means that
someone will have to handle the comma-separated list on the other side,
and is not funny at all. But I assume that you don't have control over
that.

Anyway, to give a good answer to the question, I would need to know a
few more things:
o Which version of SQL Server?
o What is a reasonable upper limit of the comma-separated string? You
could determine the current max value with this query:

SELECT MAX(listlen), AVG(listlen)
FROM (SELECT SUM(len(Material) + 2)
FROM tbl
GROUP BY Company_ID) as a

o What is the datatype of Material? That is, is varchar or nvarchar?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,

I hope it's not to late to get help on this one!

Here are the answers you are looking for:

1) I'm using SQL 2000
2) 40
3) nvarchar

To clarify the field names, it is 'material_name' instead of 'material' and
is 'to_company' instead of 'company_id'

Thanks!

Mintyman

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98A19BE4CF73BYazorman@.127.0.0.1...

Quote:

Originally Posted by

Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

>I'm working on a system migration and I need to combine data from
>multiple
>rows (with the same ID) into one comma separated string. This is how the
>data is at the moment:
>>
>Company_ID Material
>0x00C00000000053B86 Lead
>0x00C00000000053B86 Sulphur
>0x00C00000000053B86 Concrete
>>
>I need it in the following format:
>Company_ID Material
>0x00C00000000053B86 Lead, Sulphur, Concrete
>>
>There is no definite number of materials per Company.
>>
>I have read the part of
>http://www.sommarskog.se/arrays-in-sql.html#iterative that talks about
>'The Iterative Method' but my knowledge of SQL is very limited and I
>don't know how to use this code to get what I need.

>
And that article covers the opposite process - unpacking the list.
>
Composing the list is less funny, because it produces a result which
violates basic principles in a relational database: no repeating groups.
That is not to say that it's a stupid thing to ask for; it's not strange
to ask for this format in reporting. I get a little nervous when you
say that you are working with system migraton, because that means that
someone will have to handle the comma-separated list on the other side,
and is not funny at all. But I assume that you don't have control over
that.
>
Anyway, to give a good answer to the question, I would need to know a
few more things:
o Which version of SQL Server?
o What is a reasonable upper limit of the comma-separated string? You
could determine the current max value with this query:
>
SELECT MAX(listlen), AVG(listlen)
FROM (SELECT SUM(len(Material) + 2)
FROM tbl
GROUP BY Company_ID) as a
>
o What is the datatype of Material? That is, is varchar or nvarchar?
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

I hope it's not to late to get help on this one!
>
Here are the answers you are looking for:
>
1) I'm using SQL 2000
2) 40
3) nvarchar

I the longest list would be 40 characters, this means that there are not
that many materials per company. Since you said no limit, I was afraid
that there was a risk that you could exceed the limit of 4000 for an
nvarchar. In that case, you would have been in real dire straits. Unless
you had been on SQL 2005 where this would have been much simpler.

Here is an example of a query that runs in Northwind. First run:

select max(cnt) from
(select OrderID, cnt = COUNT(*)
from [Order Details]
group by OrderID) s

(but translated to your database). This gives the longest list in number
of elements. In case of Northwind the returned number is 25 which is a tad
many. With a maximum of 40 characters per list, a maximum of seven seems
reasonable. Using that number, here is a query for Northwind that
returns a comma-separated lists per order:

SELECT OrderID,
MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END) +
coalesce(MAX(CASE OD.rowno WHEN 2 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 3 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 4 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 5 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 6 THEN ', ' + P.ProductName END), '') +
coalesce(MAX(CASE OD.rowno WHEN 7 THEN ', ' + P.ProductName END), '')
FROM (SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD
JOIN Products P ON P.ProductID = OD.ProductID
GROUP BY OD.OrderID
ORDER BY OD.OrderID

If your maximum number is 8, you will need to add one more line.

Caveat: the performance of this is not fantastic. The big culprit is
the SELECT that computes the row number. If you have millions and millions
of rows in that table, you may bave to find a different way to compute
the row number. One way would to be bounce the data over a temp table
with an IDENTITY column. But before you go that route, try a query like
the one above.

If you need to compose many of these queries, I would suggest that you
look into the third-party tool RAC, http://www.rac4sql.net/ which can
help you to generate such queries.

Thanks for the script. The difference between the Northwind database and
mine is that all the data I want to get access to is in one table (unlike
Northwind where it is spread over [order details[ and [products]. I tried
modifying the script but it doesn't work:

SELECT to_company,
MAX(CASE OD.rowno WHEN 1 THEN Material_Name END) +
coalesce(MAX(CASE OD.rowno WHEN 2 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 3 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 4 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 5 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 6 THEN ', ' + Material_Name END), '') +
coalesce(MAX(CASE OD.rowno WHEN 7 THEN ', ' + Material_Name END), '')
FROM Material__Bridge AS OD
GROUP BY OD.to_company
ORDER BY OD.to_company

It says there is an invalid column name 'rowno' - I guess this is right
because there is no column with that name in my database! However, when I
check in Northwind, there isn't one called that there either!

Any ideas?

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98AED9C3878CYazorman@.127.0.0.1...

Quote:

Originally Posted by

Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

>I hope it's not to late to get help on this one!
>>
>Here are the answers you are looking for:
>>
>1) I'm using SQL 2000
>2) 40
>3) nvarchar

>
I the longest list would be 40 characters, this means that there are not
that many materials per company. Since you said no limit, I was afraid
that there was a risk that you could exceed the limit of 4000 for an
nvarchar. In that case, you would have been in real dire straits. Unless
you had been on SQL 2005 where this would have been much simpler.
>
Here is an example of a query that runs in Northwind. First run:
>
select max(cnt) from
(select OrderID, cnt = COUNT(*)
from [Order Details]
group by OrderID) s
>
(but translated to your database). This gives the longest list in number
of elements. In case of Northwind the returned number is 25 which is a tad
many. With a maximum of 40 characters per list, a maximum of seven seems
reasonable. Using that number, here is a query for Northwind that
returns a comma-separated lists per order:
>
SELECT OrderID,
MAX(CASE OD.rowno WHEN 1 THEN P.ProductName END) +
coalesce(MAX(CASE OD.rowno WHEN 2 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 3 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 4 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 5 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 6 THEN ', ' + P.ProductName END), '')
+
coalesce(MAX(CASE OD.rowno WHEN 7 THEN ', ' + P.ProductName END), '')
FROM (SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD
JOIN Products P ON P.ProductID = OD.ProductID
GROUP BY OD.OrderID
ORDER BY OD.OrderID
>
If your maximum number is 8, you will need to add one more line.
>
Caveat: the performance of this is not fantastic. The big culprit is
the SELECT that computes the row number. If you have millions and millions
of rows in that table, you may bave to find a different way to compute
the row number. One way would to be bounce the data over a temp table
with an IDENTITY column. But before you go that route, try a query like
the one above.
>
If you need to compose many of these queries, I would suggest that you
look into the third-party tool RAC, http://www.rac4sql.net/ which can
help you to generate such queries.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

I could have done the script with product ids instead of product names
but that seemed boring.

Quote:

Originally Posted by

The column rowno is defined in the derived table. I suggest that you study
my query a little closer, and try to understand what it's actually doing.

It might be that you want to be spoon-fed a solution, but I have this funny
idea that I like to help people to help themselves. That is, when I post a
solution, I hope that people do not only use it, but also try to understand
how it works, so that the next time they run into a similar problem, they
now have something in their toolbox that they can apply.

I totally agree with not being spoon fed! I'm sorry I came across as wanting
to be. I'll try and work out what your script is doing :o) Thanks for your
help!

Mintyman

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns98AF7D09BCD42Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

>Thanks for the script. The difference between the Northwind database and
>mine is that all the data I want to get access to is in one table (unlike
>Northwind where it is spread over [order details[ and [products].

>
I could have done the script with product ids instead of product names
but that seemed boring.
>

Quote:

Originally Posted by

>It says there is an invalid column name 'rowno' - I guess this is right
>because there is no column with that name in my database! However, when I
>check in Northwind, there isn't one called that there either!

>
The column rowno is defined in the derived table. I suggest that you study
my query a little closer, and try to understand what it's actually doing.
>
It might be that you want to be spoon-fed a solution, but I have this
funny
idea that I like to help people to help themselves. That is, when I post a
solution, I hope that people do not only use it, but also try to
understand
how it works, so that the next time they run into a similar problem, they
now have something in their toolbox that they can apply.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Mintyman (mintyman@.ntlworld.com) writes:

Quote:

Originally Posted by

I totally agree with not being spoon fed! I'm sorry I came across as
wanting to be. I'll try and work out what your script is doing :o)

There is one thing I should have pointed out. In my query there was this
part:

(SELECT a.OrderID, a.ProductID,
rowno = (SELECT COUNT(*)
FROM [Order Details] b
WHERE b.OrderID = a.OrderID
AND b.ProductID <= a.ProductID)
FROM [Order Details] a) AS OD

That is a *derived table*. A derived table is logically a temp table in
the query so to speak, but not materialised, and the actually computation
order can be different as long as the result is the same. Derived tables
is an enormously powerful tool to build complex queries with, and saves
you from using real temp tables.

Wednesday, March 21, 2012

Script Task Variables

script task: there should be another way to select variables than the comma seperated list

One has to type in a whole list of variables, hoping not to make any mistakes

IntelliSense for example?

But hey, I'm not complaining...

greets

There is! You can do it in code.

Writing to a variable from a script task
(http://blogs.conchango.com/jamiethomson/archive/2005/02/09/964.aspx)

Still no intellisense though!!

I highly recommend you use the code option because this minimises the risk of variable locking. I plan to blog about this soon and have written about it in an upcoming article in SQL Server Standard.

-Jamie

|||

ok, I'll do it your way

thanks

|||

Dear Jamie,

Your method works fine for 1 variable.
I assume you know this, but for other readers of this thread:

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/T_Microsoft_SqlServer_Dts_Runtime_VariableDispenser.htm

quoting:

There are two scenarios for using the variable dispenser.

You want just one variable. In this scenario, call LockOneForRead or LockOneForWrite, and a collection with one element is returned.

You want several variables. In this scenario, call LockForRead and LockForWrite several times, one for each variable. This builds up two lists, one list that contains variables for reading and a list of variables for writing. Next, call GetVariables, which gives you a collection that contains all of the locked variables. If GetVariables succeeds, the two lock lists, which are the lists of variable names, not actual locks, is cleared.

To clear the locks, call Unlock on the collection when finished to explicitly release the locks. This unlocks the variables themselves. If GetVariables fails, the lists remain unchanged, and you can call GetVariables again. If you still do not succeed, call Reset to clear the lists and bring the variable dispenser back to its initial state.

Cheers,

Tom

Friday, March 9, 2012

Script for comma seperated values

To get rid of redundant data in a table, my cleint will be providing
something like this:

IDtokeep Ids to delete
34 24,35,49
12 14,178,1457
54 32,65,68

I have to write a script for each of the above rows which looks like
this:
-----------
update sometable
set id = 34
where id in (24,35,49)

delete from sometable
where id in (24,35,49)
-----------
As I said I have to do this for EACH row. Can I somehow automate this
or will I need to write to same script for each row (there are about
5000 rows in this audit table)

Any help is highly appreciated.

Here is the DDL and inserts for the audit table.

IF object_id(N'dbo.dataclean','U') is not null
DROP TABLE [dbo].[dataclean]
GO

CREATE TABLE [dataclean] (
[IdTokeep] int NULL ,
[IdsTodelete] varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL )
GO

INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])
VALUES(34,'24,35,49')
INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])
VALUES(12,'14,178,1457')
INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])
VALUES(54,'32,65,68')
GOIf this is a one time thing then please use the following sql server
function to parse this.

The syntax would be:

update sometable set id = 34 where id in
dbo.fnStringToTable('24,35,49',',')
delete from sometable where id in dbo.fnStringToTable('24,35,49',',')

What I recommend is create a table in SQL from CSV file and populate
another table like the structure below. you can use this function to
populate this table.
MyTable:
IDToKeep IDToDelete
34 24
34 35
34 49
12 14
12 178
12 1457

and run the following statement
update sometable set id = b.idtokeep
from mytable where sometable.id=mytable.idtodelete

delete sometable where id in (select idtodelete from mytable)

The above script is not tested. so make sure you test them before you
do anythign with that. Below is the code to create the function
dbo.fnStringToTable. I hope this helps.

CREATE FUNCTION dbo.fnStringToTable
(
@.str varchar(8000),@.delim varchar(5)
)
RETURNS @.ValueStr TABLE (value varchar(500))
AS
/************************************************** ****************************
**Name: fnStringToTable
**Desc: Parses the input parameter string with the delimiter
**
**Return values: table @.valuestr (value varchar(500))
**
**
**Parameters:
**Input
** ----
**@.str - delimited string ex. . 1,2,3 max length is 8000 characters
**@.delim - delimiter to parse @.str ex. ",","-" max length is 5
characters
**Auth: Ramesh Thalluru
**Date: 07/29/2003
************************************************** *****************************
**Change History
************************************************** *****************************
**Date:Author:Description:
**------------------
**
************************************************** *****************************/
BEGIN
declare @.str1 varchar(2000), @.len int, @.endPos int, @.stPos int,
@.rightLen int, @.tmpint int, @.tmpstr varchar(8000)
-- if the string is empty or null return without anything
if ( @.str=NULL or len(ltrim(rtrim(@.str)))=0 )
return

select @.str1=rtrim(ltrim(@.str))
select @.str=@.str1
select @.len=len(@.str), @.endPos=0, @.stPos=-1, @.rightLen=0
while @.stPos <> 0
begin
select @.str1=right(@.str, @.len-@.rightLen)
select @.stPos=charindex(@.delim,@.str1)
select @.rightLen=@.rightLen+@.stPos
if @.stPos <> 0
begin
insert into @.ValueStr(value)
select rtrim(ltrim(left(@.str1,@.stPos-1)))
end
else
begin
insert into @.ValueStr(value)
select ltrim(rtrim(@.str1))
end
end
RETURN
END

muza...@.hotmail.com wrote:
> To get rid of redundant data in a table, my cleint will be providing
> something like this:
> IDtokeep Ids to delete
> 34 24,35,49
> 12 14,178,1457
> 54 32,65,68
>
> I have to write a script for each of the above rows which looks like
> this:
> -----------
> update sometable
> set id = 34
> where id in (24,35,49)
> delete from sometable
> where id in (24,35,49)
> -----------
> As I said I have to do this for EACH row. Can I somehow automate this
> or will I need to write to same script for each row (there are about
> 5000 rows in this audit table)
> Any help is highly appreciated.
> Here is the DDL and inserts for the audit table.
> IF object_id(N'dbo.dataclean','U') is not null
> DROP TABLE [dbo].[dataclean]
> GO
>
> CREATE TABLE [dataclean] (
> [IdTokeep] int NULL ,
> [IdsTodelete] varchar (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
> GO
> INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])
> VALUES(34,'24,35,49')
> INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])
> VALUES(12,'14,178,1457')
> INSERT INTO [dataclean] ([IdTokeep],[IdsTodelete])
> VALUES(54,'32,65,68')
> GO|||I am sorry that you have sucha bad client. You should break this apart
in the front end, but if you are totally screwed, try this:

Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:

CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);

INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
...

This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.

CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL PRIMARY KEY,
parm INTEGER NOT NULL);

It makes life easier if the lists in the input strings start and end
with a comma. You will need a talbe of sequential numbers -- a
standard SQL programming trick, Now, the real query, in SQL-92 syntax:

INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING ( ',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;

The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma.

You can then write:

SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);

You would never write a T-SQL procedure, if you can avoid it.|||(muzamil@.hotmail.com) writes:
> To get rid of redundant data in a table, my cleint will be providing
> something like this:
> IDtokeep Ids to delete
> 34 24,35,49
> 12 14,178,1457
> 54 32,65,68

Undoubtedly it would be a whole lot easier if your client could just
give you plain tuples:

34 24
34 35
34 49
12 14
12 178

Then it's all a plain update statement and a plain delete.

With the current scheme, you need to run a string-to-table function,
and you need to loop row by row. (In SQL 2000. In SQL 2005 you can
do it in one statement, but you still need the string-to-table
function.)

> I have to write a script for each of the above rows which looks like
> this:
> -----------
> update sometable
> set id = 34
> where id in (24,35,49)
> delete from sometable
> where id in (24,35,49)
> -----------
> As I said I have to do this for EACH row. Can I somehow automate this
> or will I need to write to same script for each row (there are about
> 5000 rows in this audit table)

Well, you can actually do it without the string-to-table function,
with some manual intervention:

SELECT 'UPDATE somtable SET id = ' + ltrim(str(IdTokeep)) +
' where id in (' +
IdsTodelete + ')
DELETE sometable where id in (' + IdsTodelete + ')'
FROM dataclean

And then cut and paste result.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Friday, March 23, 2012

Script to combine multiple rows into 1 single row

Wednesday, March 21, 2012

Script Task Variables

Friday, March 9, 2012

Script for comma seperated values

Scope parameter

Blog Archive

About Me