Concatenate strings from different rows

Hello.

I have a table like:

Id Name Description OrderId
1 Microsoft This is a 0
1 Microsoft huge company. 1

I need to create a select query that will concatenate description for both entries and output it like

Microsoft - This is a huge company.

Try using the USE XML PATH () as part of the select statement; maybe something like:

Code Snippet

declare @.testData table
( id integer,
[Name] varchar(10),
Description varchar(15),
orderId integer
)
insert into @.testData
select 1, 'Microsoft', 'This is a', 0 union all
select 1, 'Microsoft', 'huge company.', 1

select distinct
[Name] + ' - ' +
( select description + ' ' as [text()]
from @.testData b
where a.id = b.id
order by orderId
for xml path ('')
) as outputString
from @.testData a

/*
outputString
--
Microsoft - This is a huge company.
*/

Concatenate string

Please help me with this if you can.
I have one table with CustomerID and some other data.
In other table i have CustomerID(the link with the first table) and Agent
The relation of the first with the second one is ONE TO MANY.
I want something like this:
Customer,'Agent1,Agent2,Agent3'

Is it possible.
Please help :)looks like you are trying to reverse pivot...

1) Are there always three agent rows per customer id ?
2) Is there a field which identifies the agent number per customer id?

throw up the ddl to these tables|||First Table
CustomerID|CustomerName

SecondTable
CustomerID|AgentName

Ex Records:

First Table
1| Gigi
2|Vasile

Second Table
1|Ionescu
1|Vasilescu
1|George
2|Marin
2|Preda

I want:
1|'Gigi'|'Ionescu,Vasilescu,George'
2|'Vasile'|'Marin,Preda'|||I did find a solution.
But i'm not sure that is the best one.
I use a function in MS SQL Server and a Cusor inside.
Here it is the function.
Anyway, i know that Cursors are slowly so please give me another solution if you know.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION admin_GetAgentsNames (@.CustomerId int)
RETURNS varchar(1000)
AS

BEGIN
DECLARE @.AgentsNames VARCHAR(1000)
DECLARE @.AgentFName VARCHAR(50)
DECLARE @.AgentLName VARCHAR(50)
DECLARE @.AgentId INT
SET @.AgentsNames = ''
DECLARE curGetAgents CURSOR FOR
SELECT DISTINCT AgentId,ISNULL(c.FNAME,''),ISNULL(c.LNAME,'')
FROM tbl_customerToLocalAgent_Pass AS a
JOIN tbl_Customer AS b ON a.AgentId=b.CustomerId
JOIN tbl_CustomerDetails AS c ON b.CustomerId=c.CustomerId
AND b.PreferredLanguageId=c.LanguageId
WHERE a.CustomerId=@.CustomerId

--Opent the cursor
OPEN curGetAgents
--Fetch the first record
FETCH NEXT FROM curGetAgents INTO @.AgentId,@.AgentFName,@.AgentLName

WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
--Concatenate the names
SET @.AgentsNames = @.AgentsNames + ',' + @.AgentFName + ' ' + @.AgentLName
--Get the next row
FETCH NEXT FROM curGetAgents INTO @.AgentId,@.AgentFName,@.AgentLName
END
--Close cursor
CLOSE curGetAgents
DEALLOCATE curGetAgents

RETURN(RIGHT(@.AgentsNames,LEN(@.AgentsNames)))

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Concatenate Rows into Columns

I have a table 2 columns (Object and Weight) with data like below:

Object Weight(lb)
table 5
Chair 6
Computer 3
Computer 5
TV 20
TV 15
Radio 10
Computer 10

Question: I would like to create a new table with one column that would join the above two columns like below

Object
table 5
Chair 6
Computer 3, 5, 10
TV 20, 15
Radio 10

Thanks for your help in advance.

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

|||

What if I do not know the Max number of Weights for each object. eg. an object may have n number of weights in the table.

Thanks

|||

I would guess a maximum possible number in this solution. For example:

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 4 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 5 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 6 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 7 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 8 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

By the way, are you using SQL Server 2005? There are other solutions to handle this one.

|||

Yes, I am using sql server 2005

Thanks

|||

In SQL 2005, you can try like this

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

with CTE (Object,Weight1) as

(

select Object,Weight1=cast(Weight as varchar(50)) from tbl

union all

select a.Object,Weight1=convert(varchar(24),Weight1)+','+convert(varchar(25),a.Weight)

from tbl a inner loop join CTE b

on a.Object=b.Object and patindex('%'+convert(varchar(50),a.Weight)+'%',Weight1)<1

)

select distinct Object, max(Weight1) from CTE

Group by Object

|||

--We can use CTE in SQL Server 2005. The recursive function will take care of the number of records for the same object in your table.

With MyCTE(Object, Weight, Weights, myNum) AS

(SELECT a.Object, CONVERT(varchar(50), MIN(a.Weight)) as col1, CONVERT(varchar(50),(a.Weight)) as Weights, 1 as myNum

FROM mergeTable1$ a GROUP BY a.Object, CONVERT(varchar(50),(a.Weight))

UNION ALL

SELECT b.Object, CONVERT(varchar(50), b.Weight), CONVERT(varchar(50), (c.Weights + ',' + b.Weight)), c.myNum+1 as myNum

FROM mergeTable1$ b INNER JOIN MyCTE c ON b.Object=c.Object

WHERE b.Weight>c.Weight

)

SELECT a.Object, Weights FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Object FROM MyCTE a1

group by a1.Object) b on a.Object=b.Object AND a.myNum= b.myNumMax ORDER BY a.Object

|||

Yet another way to do it, using data from one of the other posts, and the 2005 techniques from:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

SELECT
Object,
Weights = LEFT(o.list, LEN(o.list)-1)
FROM
(select distinct object from tbl) as tbl --if these values are defined in another related table it is more clear
CROSS APPLY
(
SELECT CONVERT(VARCHAR(12), Weight) + ',' AS [text()]
FROM tbl as t
WHERE t.object = tbl.object
ORDER BY weight
FOR XML PATH('')
) o (list)

|||If you don't mind, what does MYCTE stand for ?|||CTE (Common Table Expression) is a new function in SQL Server 2005. You can look it up from Book Online. MyCTE is a name like you would call a table mytable in your database. I like Louis's solution a lot. It uses another new CROSS APPLY funtion in SQL Server 2005 too.|||

For some ideas, see:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

--

Anith

|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

Concatenate Rows into Columns

I have a table 2 columns (Object and Weight) with data like below:

Object Weight(lb)
table 5
Chair 6
Computer 3
Computer 5
TV 20
TV 15
Radio 10
Computer 10

Question: I would like to create a new table with one column that would join the above two columns like below

Object
table 5
Chair 6
Computer 3, 5, 10
TV 20, 15
Radio 10

Thanks for your help in advance.

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

|||

What if I do not know the Max number of Weights for each object. eg. an object may have n number of weights in the table.

Thanks

|||

I would guess a maximum possible number in this solution. For example:

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 4 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 5 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 6 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 7 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 8 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

By the way, are you using SQL Server 2005? There are other solutions to handle this one.

|||

Yes, I am using sql server 2005

Thanks

|||

In SQL 2005, you can try like this

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

with CTE (Object,Weight1) as

(

select Object,Weight1=cast(Weight as varchar(50)) from tbl

union all

select a.Object,Weight1=convert(varchar(24),Weight1)+','+convert(varchar(25),a.Weight)

from tbl a inner loop join CTE b

on a.Object=b.Object and patindex('%'+convert(varchar(50),a.Weight)+'%',Weight1)<1

)

select distinct Object, max(Weight1) from CTE

Group by Object

|||

--We can use CTE in SQL Server 2005. The recursive function will take care of the number of records for the same object in your table.

With MyCTE(Object, Weight, Weights, myNum) AS

(SELECT a.Object, CONVERT(varchar(50), MIN(a.Weight)) as col1, CONVERT(varchar(50),(a.Weight)) as Weights, 1 as myNum

FROM mergeTable1$ a GROUP BY a.Object, CONVERT(varchar(50),(a.Weight))

UNION ALL

SELECT b.Object, CONVERT(varchar(50), b.Weight), CONVERT(varchar(50), (c.Weights + ',' + b.Weight)), c.myNum+1 as myNum

FROM mergeTable1$ b INNER JOIN MyCTE c ON b.Object=c.Object

WHERE b.Weight>c.Weight

)

SELECT a.Object, Weights FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Object FROM MyCTE a1

group by a1.Object) b on a.Object=b.Object AND a.myNum= b.myNumMax ORDER BY a.Object

|||

Yet another way to do it, using data from one of the other posts, and the 2005 techniques from:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

SELECT
Object,
Weights = LEFT(o.list, LEN(o.list)-1)
FROM
(select distinct object from tbl) as tbl --if these values are defined in another related table it is more clear
CROSS APPLY
(
SELECT CONVERT(VARCHAR(12), Weight) + ',' AS [text()]
FROM tbl as t
WHERE t.object = tbl.object
ORDER BY weight
FOR XML PATH('')
) o (list)

|||If you don't mind, what does MYCTE stand for ?|||CTE (Common Table Expression) is a new function in SQL Server 2005. You can look it up from Book Online. MyCTE is a name like you would call a table mytable in your database. I like Louis's solution a lot. It uses another new CROSS APPLY funtion in SQL Server 2005 too.|||

For some ideas, see:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

--

Anith

|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

Concatenate Rows

Hi

I have a table similar to the following:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

I need the following output:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222

Could someone please point me in the right direction?

Cheers

You could to use SELECT FOR XML PAHT with empty tag:

Code Snippet

create table t2

(

Date datetime,

ID int,

Name varchar(20),

JobNumber varchar(20),

JobType varchar(20)

)

go

insert into t2 values('12/12/2007', 123456,'Fred Smith','111111','Full Day')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 222222','Half Day AM')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 333333','Half Day PM')

select

replace( (SELECT name + '##' FROM t2 as d where d.ID=m.ID FOR XML PATH('')), '##', char(10)+char(13) ) as CName

,ID from t2 m group by ID

|||Hi Kosinsky,
Your querry is not working in SQL200 is it for SQL 2005 or it will run properly in sql2000 also if not then wht will be the querry for sql2000,

I got the following error when i am trying to run your select querry in sql2000

Code Snippet

Server: Msg 170, Level 15, State 1, Line 1Line 1:

Incorrect syntax near 'XML'.

|||

My query use SELECT FOR XML PATH. Its SQL Server 2005 feature.

For SQL Server 2000 you could use FOR XML RAW and two additional replaces:

Code Snippet

select

replace

(

replace

(

replace( (SELECT name as t FROM t2 as d where d.ID=m.ID FOR XML RAW('t')), '"/><t t="', char(10)+char(13))

,'<t t="',''

)

,'"/>',''

)

,ID

from t2 m group by ID

|||Hi,
Still its giving me the same error.?

Code Snippet

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.

|||

Sorry, but me solution doesn't work on SQL Server 2000. Because FOR XML is not valid in subselections

|||

Thanks for the replies Konstantin Kosinsky, but I'm also running SQL Server 2000.

Does anyone have any other ideas on how to achieve this please?

Cheers

|||

From what I can tell, all the easy solutions for this are in SQL2005. SQL2000 solutions are much messier. Try searching this forum for words like aggregate and concatenate. There are a few that might help, like this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125302&SiteID=1

Note Umachandar's solution I think should work on 2000. MRys', while much neater, relies on having 2005.

Good luck.|||

Cheers Cringing Dragon, great find. I used the post on the link you provided by Umachandar Jayachandran - MS.

The SQL of which is:

Code Snippet

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, count(*) as seq
from your_table as t1
join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment
) as t3
group by t3.id;

Thank you all for your help.

Concatenate Rows

Hi

I have a table similar to the following:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

I need the following output:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222

Could someone please point me in the right direction?

Cheers

You could to use SELECT FOR XML PAHT with empty tag:

Code Snippet

create table t2

(

Date datetime,

ID int,

Name varchar(20),

JobNumber varchar(20),

JobType varchar(20)

)

go

insert into t2 values('12/12/2007', 123456,'Fred Smith','111111','Full Day')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 222222','Half Day AM')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 333333','Half Day PM')

select

replace( (SELECT name + '##' FROM t2 as d where d.ID=m.ID FOR XML PATH('')), '##', char(10)+char(13) ) as CName

,ID from t2 m group by ID

|||Hi Kosinsky,
Your querry is not working in SQL200 is it for SQL 2005 or it will run properly in sql2000 also if not then wht will be the querry for sql2000,

I got the following error when i am trying to run your select querry in sql2000

Code Snippet

Server: Msg 170, Level 15, State 1, Line 1Line 1:

Incorrect syntax near 'XML'.

|||

My query use SELECT FOR XML PATH. Its SQL Server 2005 feature.

For SQL Server 2000 you could use FOR XML RAW and two additional replaces:

Code Snippet

select

replace

(

replace

(

replace( (SELECT name as t FROM t2 as d where d.ID=m.ID FOR XML RAW('t')), '"/><t t="', char(10)+char(13))

,'<t t="',''

)

,'"/>',''

)

,ID

from t2 m group by ID

|||Hi,
Still its giving me the same error.?

Code Snippet

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.

|||

Sorry, but me solution doesn't work on SQL Server 2000. Because FOR XML is not valid in subselections

|||

Thanks for the replies Konstantin Kosinsky, but I'm also running SQL Server 2000.

Does anyone have any other ideas on how to achieve this please?

Cheers

|||

From what I can tell, all the easy solutions for this are in SQL2005. SQL2000 solutions are much messier. Try searching this forum for words like aggregate and concatenate. There are a few that might help, like this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125302&SiteID=1

Note Umachandar's solution I think should work on 2000. MRys', while much neater, relies on having 2005.

Good luck.|||

Cheers Cringing Dragon, great find. I used the post on the link you provided by Umachandar Jayachandran - MS.

The SQL of which is:

Code Snippet

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, count(*) as seq
from your_table as t1
join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment
) as t3
group by t3.id;

Thank you all for your help.

Concatenate int & var char - SQL

Hi,

I am trying to write some simple SQL to join two fields within a table, the primary key is an int and the other field is a varchar.

But i am receiving the error:

'Conversion failed when converting the varchar value ',' to data type int.

The SQL I am trying to use is:

select game_no + ',' + team_name as match
from result

Thanks

As the error message says you can concatenate similar datatype values and use CONVERT or CAST functions otherwise to make them simialr.

selectConvert(Varchar,game_no)+','+ team_nameas match

from result

|||

Yep. Why the parser is so stupid that it assumes the presence of one number means all other exprssions must evaluate to a number, rather than the other way around, is a mystery to me.

concatenate columns in sql 2000

i need to concatenate two columns into one column in a table for sql 2000.Has the teacher covered this in class yet? There are multiple ways to do it, and without knowing what they've covered or what the assignment actually says it is tough to guess which answer they want.

-PatP|||I am simply trying to get some help. I am not a DBA, I am not in class for it, nor do I want to be one. I am a networking guy trying to get as much help as I can from other professionals. I have some basic familiarity with SQL but not much. I have to perform this concatenation for my job, but my company does not have a DBA either.

Can anyone help??|||If all you need is to get the job done, then I'd try using something like:UPDATE myTable
SET first_column = first_column + second_column-PatP

concatenate a string within a loop from a temp table

I need help.

I have a large table that looks like this.

(ID INT NOT NULL IDENTITY(1,1),PK INT , pocket VARCHAR(10))

1, 1, p1
2, 1, p2
3, 2, p3
4, 2, p4
5, 3, p5
6, 3, p6
7, 4, p7
8, 5, p1
9, 5, p2
10,5, p83

i would like to loop through the table and concatenate the pocket filed for all the records that has the same pk. and insert the pk and the concatenated string into another table in a timely manner.

can anyone help?

Emad

An easy way (but slow if you have a big table) to do this is to use a cursor. Bellow is a fully functional example.

Hope it helps!

DECLARE @.MyTable TABLE (ID INT NOT NULL,PK INT , pocket VARCHAR(10))

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (1, 1, 'p1')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (2, 1, 'p2')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (3, 2, 'p3')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (4, 2, 'p4')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (5, 3, 'p5')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (6, 3, 'p6')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (7, 4, 'p7')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (8, 5, 'p1')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (9, 5, 'p2')

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (10,5, 'p83')

DECLARE @.MyResult TABLE (PK Int, SumOfPockets Varchar(4000))

DECLARE @.PK Int

DECLARE @.MyString Varchar(4000)

DECLARE cMyTable CURSOR LOCAL FAST_FORWARD FOR

SELECT PK FROM @.MyTable GROUP BY PK

OPEN cMyTable

FETCH NEXT FROM cMyTable INTO @.PK

WHILE @.@.FETCH_STATUS=0

BEGIN

SET @.MyString = ''

SELECT @.MyString = @.MyString + pocket FROM @.MyTable WHERE PK = @.PK

INSERT INTO @.MyResult(PK, SumOfPockets) VALUES (@.PK, @.MyString)

FETCH NEXT FROM cMyTable INTO @.PK

END

CLOSE cMyTable

DEALLOCATE cMyTable

SELECT * FROM @.MyResult

|||

Doru,

This looks like a great solution although i have a very huge table and i ran out of resources when i tried to implement your solution. I was hopping i can do it with some loops and temp tables. do you have any other ideas.

Emad

|||i CAN'T USE LOCAL VARIABLES BECAUSE THE RESULT OF THE CONCATENATION WOULD BE MORE THAN 8000 CHAR.|||

Emadkb wrote:

i CAN'T USE LOCAL VARIABLES BECAUSE THE RESULT OF THE CONCATENATION WOULD BE MORE THAN 8000 CHAR.

If you're running on SQL Server 2005, you can use VARCHAR(MAX) and NVARCHAR(MAX) variables in your string concatenation.

|||we are still on SQL 2000|||

Hi Emad,

If Varchar(8000) is not big enough then you'll have to use Text datatype in your destination table. This will slow down the whole thing even more :-(.

A solution that should work, but which is very slow, is:

---

DECLARE @.MyTable TABLE (ID INT NOT NULL,PK INT , pocket VARCHAR(10))

INSERT INTO @.MyTable(ID, PK, pocket) VALUES (1, 1, 'p1')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (2, 1, 'p2')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (3, 2, 'p3')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (4, 2, 'p4')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (5, 3, 'p5')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (6, 3, 'p6')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (7, 4, 'p7')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (8, 5, 'p1')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (9, 5, 'p2')
INSERT INTO @.MyTable(ID, PK, pocket) VALUES (10,5, 'p83')

CREATE TABLE #MyResult (PK Int, SumOfPockets Text)
DECLARE @.PK Int, @.pocket Varchar(10), @.prev_PK Int
DECLARE @.ptrColText Varbinary(16)

DECLARE cMyTable CURSOR LOCAL FAST_FORWARD FOR
SELECT PK, pocket FROM @.MyTable

OPEN cMyTable
FETCH NEXT FROM cMyTable INTO @.PK, @.pocket

WHILE @.@.FETCH_STATUS=0
BEGIN
IF @.PK = @.prev_PK
UPDATETEXT #MyResult.SumOfPockets @.ptrColText NULL 0 @.pocket
ELSE
BEGIN
INSERT INTO #MyResult(PK, SumOfPockets)
VALUES (@.PK, @.pocket)

SELECT @.ptrColText = TEXTPTR(SumOfPockets)
FROM #MyResult WHERE PK = @.PK

SET @.prev_PK = @.PK
END

FETCH NEXT FROM cMyTable INTO @.PK, @.pocket

END
CLOSE cMyTable
DEALLOCATE cMyTable

SELECT * FROM #MyResult

---

|||

This looks exactly like i want. Excellent work. I will try it and let you know how it goes.

Emad

Concatenate a string in SQL

I have a char(10) column in a table, I would like to concatenate the string
to a variable with coma's in between the strings. I currently use a cursor.
Can any one think of a better way?
--
Thanks,
Jon AHere is a technique:
declare @.myVar varchar(1000)
set @.myVar = ''
select @.myVar = @.myVar + ', ' + myColumn from myTable
set @.myVar = substring(@.myVar,3,1000)
print @.myVar
"Jon A" wrote:

> I have a char(10) column in a table, I would like to concatenate the strin
g
> to a variable with coma's in between the strings. I currently use a cursor
.
> Can any one think of a better way?
> --
> Thanks,
> Jon A|||Here is example from http://vyaskn.tripod.com/code/cat.txt
USE pubs
GO
DECLARE @.title_ids varchar(150), @.delimiter char
SET @.delimiter = ','
SELECT @.title_ids = COALESCE(@.title_ids + @.delimiter, '') + title_id FROM
titles
SELECT @.title_ids AS [List of Title IDs]
Ana
"Jon A" wrote:

> I have a char(10) column in a table, I would like to concatenate the strin
g
> to a variable with coma's in between the strings. I currently use a cursor
.
> Can any one think of a better way?
> --
> Thanks,
> Jon A|||If you mean each column value frpm each row in the the table in a single
comma-delimited string, then no, afaik there is no way without row=based
processing. But a cursor is not the only way... You could use a while loop.
.
Declare @.Out VarChar(8000) Set @.Out = ''
Declare @.ColVal Char(10) Set @.ColVal =''
While exists (Select * From Table
Where ColName > @.ColVal)
Select @.ColVal = Min(ColName),
@.Out = @.Out + RTrim(Min(ColName)) + ','
From Table
Where ColName > @.ColVal
[Untested]
the above approach has the property that it will alphabeticize the entries
in the output tng, and ensure that duplicates are only entered once.
Or, if the Table has a primary key, and you want the duplicates entered
individually,
you can put all the PK values into a table variable and cycle through that
table, delting the records as you use them... This is equivilent to using a
cursor, and much faster...
Declare @.Out VarChar(8000) Set @.Out = ''
Declare @.Val VarChar(10)
Declare @.PK Integer
Declare @.PKs Table (PK Integer Primary Key Not Null)
Insert @.PKs(PK) Select PKColumn From TableName
While exists (Select * From @.PKs) Begin
Select @.PK = Min(PK) From @.PKs
Select @.Out = @.Out + ColName + ','
From Table Wher e PKCol = @.PK
Delete @.PKs Where PK = @.PK
End
hth,
Charly
"Jon A" wrote:

> I have a char(10) column in a table, I would like to concatenate the strin
g
> to a variable with coma's in between the strings. I currently use a cursor
.
> Can any one think of a better way?
> --
> Thanks,
> Jon A|||> I have a char(10) column in a table, I would like to concatenate the string
> to a variable with coma's in between the strings. I currently use a cursor
.
> Can any one think of a better way?
Yes! A better way is to handle presentation and formatting on the client
(that's kind of why it's called the presentation layer).
But of course, like many other inadvisable things, there are kludges to do
this in a query:
http://www.aspfaq.com/2529

Concatenate

In the SQL Query.. how do i Concatenate ?

I want to concatenate 2 of the same column and table..

example...
tableA with column id and y
tableB with column id and x and group

SELECT d.y + dd.y as PERM
FROM tableA d, tableA dd, tableB c
WHERE d.id = c.id
AND dd.id = c.groupThis is a very very difficult thing to figure out how to do.

One approach would be to look at the online documentation. While this requires advanced skills, I will try to walk your through it.

1) Open Internet Explorer (or your favorite browser)
2) Navigate to http://msdn.microsoft.com
3) Enter in a search of "sql concatenate"
4) Click on the first hyper link provided.
5) Read the page that is displayed.

In case #1 through #4 are too difficult, click here (http://msdn2.microsoft.com/en-us/library/aa276862(SQL.80).aspx) (the blue underlined part is what you click on).

In case reading a page of material is too time consuming:

USE pubs
SELECT (au_lname + ', ' + au_fname) AS Name
FROM authors
ORDER BY au_lname ASC, au_fname ASC

And finally in case the sample is too hard to follow....

Just put parenthesis around the expression!!!!!!|||well... the problem is SQL Query in Crystal Report seems not allowing me to put the a and b in the select part...

what i need to to concatenae one table of the same column ~~~

what if like your example... but instead of
(au_lname + ', ' + au_fname) AS Name

what i want is......
(au_lname + ', ' + au_lname) AS Name|||well... the problem is SQL Query in Crystal Report seems not allowing me to put the a and b in the select part...

what i need to to concatenae one table of the same column ~~~

what if like your example... but instead of
(au_lname + ', ' + au_fname) AS Name

what i want is......
(au_lname + ', ' + au_lname) AS Name

Those two look exactly the same to me... (but I have been here 19+ hours...)|||hhahaa...
i see... hmmm...

the difference is instead of Lname and Fname...

i want both Lname...

your not sleepy huh ?sqlsql

Concatanating 2 or more rows

Hi, Sounds simple but I can not figure this out.
I have 5 rows of data sharing a common id in my table.

ID NAME
22 Rick
22 John
22 Paul
22 Tom
22 Mary

The result I want on 1 line is:
Rick, John, Paul, Tom and Mary.

How can I make this so?

RickI am using CR XI, if this data is in details, go to details, select section expert, select format with multiple columns, you should see a layout tab pop up, select the size and direction you want your data to flow in, also, you may select format groups with multiple columns. If this is not what you were looking for... you can do this. Create a formula.
data1 &" "& data2 &" "& data3 &" "& data4 etc...|||Group on ID.
Create 3 formulas:
1) place in group header, suppress the formula
whileprintingrecords;
stringvar names := "";

2) place in details, suppress the section
whileprintingrecords;
stringvar names;
names := names & ", " & {table.field);

3) place in group footer
whileprintingrecords;
stringvar names;
mid(names, 2)|||One minor typo; the group footer formula should say
mid(names, 3)
or you'll have a leading space.

I've not addressed your requirement to replace the last comma with the word 'and', but you can probably work that one out yourself.

Concat tables into one row in view

If I have table1 and table2 with table2 having multiple rows tied to a
single row in table 1.

What I am trying to do is set up a view that has one row that shows
the following
table1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,
table2.row3.detail

I'd like to be able to do a select on the view and only come back with
one row per widget. If possible, I'd actually like to be able to
concat all the rows from table 2 into one column if that's possible.

table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail
- table2.row3.detail), table1.dateCreated

thx
M@.M@. (mattcushing@.gmail.com) writes:

Quote:

Originally Posted by

If I have table1 and table2 with table2 having multiple rows tied to a
single row in table 1.
>
What I am trying to do is set up a view that has one row that shows
the following
table1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,
table2.row3.detail
>
I'd like to be able to do a select on the view and only come back with
one row per widget. If possible, I'd actually like to be able to
concat all the rows from table 2 into one column if that's possible.
>
table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail
- table2.row3.detail), table1.dateCreated

SQL Server MVP Anith Sen has a couple of methods on
http://www.projectdmx.com/tsql/rowconcatenate.aspx.

--
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.mspxsqlsql

concat selected values in column of a table with cr/lf

In a column of my table i will concat two selected values (Strings). I do
this with the &-Sign.
The Problem: Is it possible to make a carriage-return and line-feed in a
column ?
--
Message posted via http://www.sqlmonster.comDid you try "Text" & vbcrlf & "Test"?
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Holger Schulz via SQLMonster.com" <forum@.nospam.SQLMonster.com> schrieb im
Newsbeitrag news:2d8df2df1adf408e87dfe4a194b2f8ef@.SQLMonster.com...
> In a column of my table i will concat two selected values (Strings). I do
> this with the &-Sign.
> The Problem: Is it possible to make a carriage-return and line-feed in a
> column ?
> --
> Message posted via http://www.sqlmonster.com|||Thanks, it works fine...
--
Message posted via http://www.sqlmonster.com

ConCat data

I need to create a csv file where it's just one giant file.
There is only one field I pull from a table called USerID(Char8). Then for
every record in the table create a file like this.
Receipents-c/n=XXXXX%Receipents-c/n=XXXXXReceipents-c/n=XXXXXReceipents-c/n=
XXXXX This will then Import into Exchange for a Distrubution List. Any IdeasSee if this link gives you some ideas
http://www.rac4sql.net/xp_execresultset.asp
Anith|||Would that not put each order on a separate line.
I need it all strung together ..as one big file...
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23QJSioLBFHA.936@.TK2MSFTNGP12.phx.gbl...
> See if this link gives you some ideas
> http://www.rac4sql.net/xp_execresultset.asp
> --
> Anith
>|||Anith has pointed you to a trick to create a file for each record/row for
your table.
Look like you want to concatenate the rows into a single string? If so, it's
probably best to do it from the client side (i.e. vb/script/etc).
Though, I am bit about your comment regarding CSV in your first
post. Perhaps, you want to clarify so we can help.
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:L92dnbdsqJ2N6WTcRVn-gg@.kconline.com...
> Would that not put each order on a separate line.
> I need it all strung together ..as one big file...
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23QJSioLBFHA.936@.TK2MSFTNGP12.phx.gbl...
>|||sorry .. I just meant to have a csv extension to the filename.
do you have an example vbscript to concat these records from the sql table
?
thanks again.
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23C0Ko0MBFHA.3492@.TK2MSFTNGP12.phx.gbl...
> Anith has pointed you to a trick to create a file for each record/row for
> your table.
> Look like you want to concatenate the rows into a single string? If so,
> it's probably best to do it from the client side (i.e. vb/script/etc).
> Though, I am bit about your comment regarding CSV in your first
> post. Perhaps, you want to clarify so we can help.
> --
> -oj
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:L92dnbdsqJ2N6WTcRVn-gg@.kconline.com...
>|||Here is a vbscript.
Main()
Sub Main()
Dim sqlcnt,rs,s
s="Begin"
Set cntsql = CreateObject("ADODB.Connection")
With cntsql
.provider = "SQLOLEDB"
.connectionstring = "Data Source=.\dev;integrated security=SSPI"
.Open
Set rs = .Execute("select OrderID from Northwind..Orders")
Do Until rs.EOF
s = s & rs.Fields("OrderID") & ","
rs.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set cntsql = Nothing
s = s & "End"
Call WriteToFile(s)
End Sub
Function WriteToFile(s)
Dim fso, tf
Set fso = CreateObject("Scripting.FileSystemObject")
Set tf = fso.CreateTextFile("c:\test.csv", True)
tf.Write(s)
tf.Close()
Set fso= Nothing
Set tf= Nothing
End Function
-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:ZLydnddhH-JSVWTcRVn-tw@.kconline.com...
> sorry .. I just meant to have a csv extension to the filename.
> do you have an example vbscript to concat these records from the sql table
> ?
> thanks again.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:%23C0Ko0MBFHA.3492@.TK2MSFTNGP12.phx.gbl...
>|||Thanks again.
"oj" <nospam_ojngo@.home.com> wrote in message
news:uyuNXkQBFHA.3368@.TK2MSFTNGP10.phx.gbl...
> Here is a vbscript.
> Main()
> Sub Main()
> Dim sqlcnt,rs,s
> s="Begin"
> Set cntsql = CreateObject("ADODB.Connection")
> With cntsql
> .provider = "SQLOLEDB"
> .connectionstring = "Data Source=.\dev;integrated security=SSPI"
> .Open
> Set rs = .Execute("select OrderID from Northwind..Orders")
> Do Until rs.EOF
> s = s & rs.Fields("OrderID") & ","
> rs.MoveNext
> Loop
> .Close
> End With
> Set rs = Nothing
> Set cntsql = Nothing
> s = s & "End"
> Call WriteToFile(s)
> End Sub
> Function WriteToFile(s)
> Dim fso, tf
> Set fso = CreateObject("Scripting.FileSystemObject")
> Set tf = fso.CreateTextFile("c:\test.csv", True)
> tf.Write(s)
> tf.Close()
> Set fso= Nothing
> Set tf= Nothing
> End Function
>
> --
> -oj
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:ZLydnddhH-JSVWTcRVn-tw@.kconline.com...
>|||oj The script worked great but...
The problem I'm having it puts an extra %Recipients/cn= at the end of the
file. The Import process that is using this output fails on this bogus
record since it doesn't have an ID attached. How can I remove this last
record from the file if it doesn't have a valid record.
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:msKdnbiYuoaxv2fcRVn-vw@.kconline.com...
> Thanks again.
>
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uyuNXkQBFHA.3368@.TK2MSFTNGP10.phx.gbl...
>|||You would need to check the returned value before concatenating it in your
vbscript.
e.g.
if rs("your_keycol")="abc" then
'it is good and concatenate
else
'it is bad and ignore
endif
Take a look at this site for help on vbscripting
http://msdn.microsoft.com/library/e...me=true

-oj
"HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
news:g5WdnagRsbodGpzfRVn-2A@.kconline.com...
> oj The script worked great but...
> The problem I'm having it puts an extra %Recipients/cn= at the end of the
> file. The Import process that is using this output fails on this bogus
> record since it doesn't have an ID attached. How can I remove this last
> record from the file if it doesn't have a valid record.
>
>
> "HoosBruin" <Hoosbruin@.Kconline.com> wrote in message
> news:msKdnbiYuoaxv2fcRVn-vw@.kconline.com...
>

concat all col2 values for each col1, and add sum(col3) (was "query help")

Hi,
Can anybody help me to create a single query? I have this problem.

CREATE TABLE t1 (
col1 VARCHAR(100)
, col2 VARCHAR(100)
, col3 INT)

INSERT INTO t1 VALUES('A001','Tom',30)
INSERT INTO t1 VALUES('A001','Rick',40)
INSERT INTO t1 VALUES('A001','Harry',10)

INSERT INTO t1 VALUES('A002','Peter',50)
INSERT INTO t1 VALUES('A002','Sam',50)

INSERT INTO t1 VALUES('A003','Fred',50)

I want a resultset like this ...
i.e col1 col2(all the values would be represented in a single row for each col1) and sum(col3)

(Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)

A001 Tom Rick Harry 80 --sum(col3)
A002 Peter Sam NULL 100
A003 Fred NULL NULL 50

Any help would be greatly appreciated !!(Note: There can be maximum three records for each col1 record,i.e for A001 there can be maximum three records)
Based on this the below works. I think it is about as efficient as it can be though verbose for the sake of transparency :)

SET NOCOUNT ON
CREATE TABLE t1 (
col1 VARCHAR(100)
, col2 VARCHAR(100)
, col3 INT)

INSERT INTO t1 VALUES('A001','Tom',30)
INSERT INTO t1 VALUES('A001','Rick',40)
INSERT INTO t1 VALUES('A001','Harry',10)
INSERT INTO t1 VALUES('A002','Peter',50)
INSERT INTO t1 VALUES('A002','Sam',50)
INSERT INTO t1 VALUES('A003','Fred',50)

SELECT Col1,
Col2a,
Col2b,
Col2c,
SUM(Col3) AS TheTotal
FROM --Pivot data
(SELECT TOP 100 PERCENT
Col1,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
ORDER BY
B.Col2) AS Col2a,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
AND B.Col2 NOT IN (SELECT TOP 1 Col2
FROM dbo.t1 AS C
WHERE C.Col1 = A.Col1
ORDER BY
C.Col2)
ORDER BY
B.Col2) AS Col2b,
(SELECT TOP 1 Col2
FROM dbo.t1 AS B
WHERE A.Col1 = B.Col1
AND B.Col2 NOT IN (SELECT TOP 2 Col2
FROM dbo.t1 AS C
WHERE C.Col1 = A.Col1
ORDER BY
C.Col2)) AS Col2c,
Col3
FROM dbo.t1 AS A
ORDER BY
Col1,
Col2) AS DerT
GROUP BY
Col1,
Col2a,
Col2b,
Col2c

DROP TABLE t1

SET NOCOUNT OFF
HTH|||Based on this the below works. I think it is about as efficient as it can be though verbose for the sake of transparency :)

Awesome, as usual...thanks a ton Pootie!!:rolleyes:|||nevermind, you have a better solution above.|||As a possible alternative, blindman's neat function here could be adapted, resulting in a much simpler query:

SELECT col1, dbo.Concat_ICD(col1) as TheNames, Sum(col3) as TheTotal
FROM t1
GROUP BY col1

http://www.dbforums.com/showthread.php?t=1605725

This would not produce the visible NULL in the result, but I was presuming that wasn't a requirement.|||The function is your best solutions, because it works for any number of records.

By the way, I wish I could take credit for that function, but it is actually one of the many things I have learned from participating in this forum over that past few years.|||Actually that was the solution I hoped to use - it allows n values to be concatenated. However I read the requirement as the return putting the names into three columns rather than one. If this isn't a requirement then defo go with Blindman's solution.|||I wanted it in three different columns.So I used Pootie's one.
Anyways,Thanks everybody for their valuable info.|||for comparison purposes, here is the equivalent query in mysql --select col1
, group_concat(col2)
, sum(col3)
from daTable
group
by col1:)|||I'm calling you on that one Rudy. Did you read the requirements carefully?|||...but here is a shorter method of coding it for SQL Server:SET NOCOUNT ON
CREATE TABLE #t1
(col1 VARCHAR(100),
col2 VARCHAR(100),
col3 INT)

INSERT INTO #t1 VALUES('A001','Tom',30)
INSERT INTO #t1 VALUES('A001','Rick',40)
INSERT INTO #t1 VALUES('A001','Harry',10)
INSERT INTO #t1 VALUES('A002','Peter',50)
INSERT INTO #t1 VALUES('A002','Sam',50)
INSERT INTO #t1 VALUES('A003','Fred',50)

select A.col1,
min(A.col2) as name1,
min(B.col2) as name2,
min(C.col2) as name3,
max(coalesce(A.col3, 0) + coalesce(B.col3, 0) + coalesce(C.col3, 0)) as col3total
from #t1 A
left outer join #t1 B on A.col1 = B.col1 and A.col2 < B.col2
left outer join #t1 C on B.col1 = C.col1 and B.col2 < C.col2
group by A.col1

drop table #t1|||I'm calling you on that one Rudy. Did you read the requirements carefully?
oh, SHEEEEEEEESH, okay :S
select col1
, group_concat(col2 separator ' ')
, sum(col3)
from t1
group
by col1|||for comparison purposes, here is the equivalent query in mysql --select col1
, group_concat(col2)
, sum(col3)
from daTable
group
by col1:)

...and that would be just great if it was a real ANSI compliant databa...

Oh, never mind|||pot? meet kettle

kettle? meet pot

:p|||That was great,thank you Blindman.And thank you all of you for your help.

Concat

Hi
I want to Concat Rows of a Column in a Table with One "Select" Order (I mean without use cursor)

Example:

Table1:
Column1
----
'a'
'b'
'c'

Result:
'abc'

Please answer me

thanks alotif you're using MySQL, use the GROUP_CONCAT function

if you're using Sybase ASE, use the LIST function

otherwise, a cursor is actually not a bad idea, because other database systems don't have a similar aggregate functionsqlsql

Computing several columns for each row in source table and joining to get result

I have come across this several times now, and I cannot figure out how to do
it better. Say I have a simple table called SourceTable:
DECLARE @.sourceTable TABLE
(
data1 INT,
data2 INT,
data3 INT,
data4 INT
)
I need to create a table (view, tv function, etc.) that looks something like
DECLARE @.resultTable TABLE
(
data1 INT,
data2 INT,
data3 INT,
data4 INT,
date1 SMALLDATETIME,
date2 SMALLDATETIME
)
where date1 and date2 are calculated (with functions) using data1...data4
from the same row plus another parameter supplied by the user. So you see
what I want is so simple: For each row in @.sourceTable, evaluate a
table-valued function getDates() that returns a single row containing date1
and date2, and join the result to produce @.resultTable. However, I can't
figure out any syntax to do this straightforwardly.
In some cases where date2 depends on date1, I can use nested queries, so I
can do something like
SELECT
data1,
data2,
data3,
data4,
date1,
date2 = getDate2(@.userInput, date1, data3, data4)
FROM (
SELECT
data1,
data2,
data3,
data4,
date1 = getDate1(@.userInput, data1, data2)
FROM
@.sourceTable
) T1
But recently, I have had several problems where it would be more efficient
and maintainable if I could return both date1 and date2 from a table-valued
function as a single row with two columns. This is because the relationship
between date1 and date2 is more complicated and they can't just be computed
sequentially. My first attempt was to write a TV function that basically
was
CREATE FUNCTION getDates (@.userInput INT, @.data1 INT, @.data2 INT, @.data3
INT, @.data4 INT)
RETURNS @.dates TABLE (date1 SMALLDATETIME, date2 SMALLDATETIME) AS
BEGIN
DECLARE @.date1 SMALLDATETIME
SET @.date1 = getDate1(@.userInput, @.data1, @.data2)
DECLARE @.date2 SMALLDATETIME
SET @.date2 = getDate2(@.userInput, @.data3, @.data4)
IF (@.date1 < @.date2)
SET @.date1 = getDate1(@.date2, @.data1, @.data2)
INSERT INTO @.dates
SELECT @.date1, @.date2
RETURN
END
I tried to join the function with the source table to get my result table as
follows:
SELECT
ST.data1,
ST.data2,
ST.data3,
ST.data4,
D.date1,
D.date2
FROM @.sourceTable ST
INNER JOIN getDates(
@.userInput,
ST.data1,
ST.data2,
ST.data3,
ST.data4) D
but SQL Server always complains when it reaches the 'ST' in the second
argument of getDates(), because apparently ST is not available in that
context. I tried using a cursor to evaluate getDates() for each row in
@.sourceTable and join the result to produce @.resultTable, but something was
just wrong and the query batch would never finish executing in query
analyzer. (I debugged and found that the cursor was implemented properly,
it was just extremely slow or was hanging in QA.) For now, I am using a
several-level-deep nested query that performs the logic of of my getDates()
function. Each query level performs one calculation or condition on one of
the two dates, and the rest of the columns just get carried along. For
example:
SELECT
data1,
data2,
data3,
data4,
date1 = CASE WHEN (date1 < date2)
THEN getDate1(date2, data1, data2)
ELSE date1
END,
date2
FROM (
SELECT
data1,
data2,
data3,
data4,
date1,
date2 = getDate2(@.userInput, data3, data4)
FROM (
SELECT
data1,
data2,
data3,
data4,
date1 = getDate1(@.userInput, data1, data2)
FROM
@.sourceTable
) RT1
) RT2
The query is actually a few levels deeper because I have to calculate other
things based on date1, and there are many more columns. This is horrible in
terms of readability and maintanability because the logic is distributed
throughout each level of the query, and I have to repeat all the columns at
each level. If I could return more than one column from a correlated
subquery, I would be fine, but I don't believe this is possible. Can
someone please help?Well, at least I know it wasn't just me. Thanks!
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23hvbu$gEGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Dustbort,
> SQL Server 2000 and earlier do not support "correlated joins",
> which is what you are trying to write. In your example, the
> right-hand table is a table-valued function that is a different
> table for each row of the left-hand table.
> In SQL Server 2005, this can be done with the new
> APPLY operator. In 2000, there is no easy way,
> though it's possible that there is an easier way to solve
> your specific problem.
> Steve Kass
> Drew University
>
> dustbort wrote:
>

Computing a Grand Total

I have inherited a table that has entries such as:
AccountName, [Invoice No], [Sales Price] Qty, etc...
I would like to have a SubTotal and GrandTotal. So, I have a select
statement something like this:
Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty as
SubTotal
FROM ...
That works fine, I get an extra column with the product of Qty and Sales
Price.
However, how would I get a Grand Total per invoice? I can do it in VB.Net,
but would like to see how to do it in SQL.
TIA,
PaoloCOMPUTE SUM(SubTotal) BY [Invoice No]
Here's the Books ONline URL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/69009df2-dba5-4bcb-b2ae-
a7502537cb3e.htm
HTH. Ryan
"Paul" <PaulContactMe@.TheCornerStore.com> wrote in message
news:LesBf.1078$v81.173@.fe12.lga...
>I have inherited a table that has entries such as:
> AccountName, [Invoice No], [Sales Price] Qty, etc...
> I would like to have a SubTotal and GrandTotal. So, I have a select
> statement something like this:
> Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty
> as SubTotal
> FROM ...
> That works fine, I get an extra column with the product of Qty and Sales
> Price.
> However, how would I get a Grand Total per invoice? I can do it in VB.Net,
> but would like to see how to do it in SQL.
> TIA,
> Paolo
>
>|||Try
SELECT [Invoice No], SUM([Sales Price] * Qty)
FROM [YourTable]
GROUP BY [Invoice No]
If you want grand totals for AccountName and [Invoice No]
SELECT AccountName, [Invoice No], SUM([Sales Price] * Qty) AS "Total"
FROM [YourTable]
GROUP BY AccountName, [Invoice No]
WITH ROLLUP
The out put of the above query will have rows like
AccountName [Invoice No] Total
---
Some Account NULL $3.50
A row with a NULL in the [Invoice No] column indicates the grand total
for that AccountName. You will also get a row like
AccountName [Invoice No] Total
---
NULL NULL $1500.78
which shows the total for all invoices and accounts.
"Paul" wrote:

> I have inherited a table that has entries such as:
> AccountName, [Invoice No], [Sales Price] Qty, etc...
> I would like to have a SubTotal and GrandTotal. So, I have a select
> statement something like this:
> Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty
as
> SubTotal
> FROM ...
> That works fine, I get an extra column with the product of Qty and Sales
> Price.
> However, how would I get a Grand Total per invoice? I can do it in VB.Net,
> but would like to see how to do it in SQL.
> TIA,
> Paolo
>
>|||Try following examples on pubs database. You will have to use COMPUTE clause
.
use pubs
go
select ord_num, stor_id, qty from sales
order by ord_num
compute sum(qty) by ord_num
--using computed expression query will look like.
select ord_num, stor_id, (qty * 2) as subtotal from sales
order by ord_num
compute sum(qty * 2) by ord_num
"Paul" wrote:

> I have inherited a table that has entries such as:
> AccountName, [Invoice No], [Sales Price] Qty, etc...
> I would like to have a SubTotal and GrandTotal. So, I have a select
> statement something like this:
> Select AccountName, Qty, [Invoice No], [Sales Price], [Sales Price] * Qty
as
> SubTotal
> FROM ...
> That works fine, I get an extra column with the product of Qty and Sales
> Price.
> However, how would I get a Grand Total per invoice? I can do it in VB.Net,
> but would like to see how to do it in SQL.
> TIA,
> Paolo
>
>|||I agree with Mark. Using ROLLUP on a GROUP BY is the far better choice,
since it will always be returned in one result set. COMPUTE is pretty ugly
to deal with for the user program.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:799AF000-43FC-483A-94E1-D5DFC3FA3CEF@.microsoft.com...
> Try
> SELECT [Invoice No], SUM([Sales Price] * Qty)
> FROM [YourTable]
> GROUP BY [Invoice No]
> If you want grand totals for AccountName and [Invoice No]
> SELECT AccountName, [Invoice No], SUM([Sales Price] * Qty) AS "Total"
> FROM [YourTable]
> GROUP BY AccountName, [Invoice No]
> WITH ROLLUP
> The out put of the above query will have rows like
> AccountName [Invoice No] Total
> ---
> Some Account NULL $3.50
> A row with a NULL in the [Invoice No] column indicates the grand total
> for that AccountName. You will also get a row like
>
> AccountName [Invoice No] Total
> ---
> NULL NULL $1500.78
> which shows the total for all invoices and accounts.
>
> --
> "Paul" wrote:
>

computed value

I've added a column to my table as a computed value, pretty much only to
populate it for the existing data. I want to do this, then drop the compute
d
value, make it's new column NOT NULL and lay over it a unique constraint.
Can I drop the computed value w/out dropping the column?
-- Lynn"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:4052E967-8301-4F0D-A2FF-C721068FC0B3@.microsoft.com...
> I've added a column to my table as a computed value, pretty much only to
> populate it for the existing data. I want to do this, then drop the
> computed
> value, make it's new column NOT NULL and lay over it a unique constraint.
> Can I drop the computed value w/out dropping the column?
> -- Lynn
Why not add it as NOT NULL and default it to zero, or x or whatever.
Then run and update on the column and compute your new values.
Better yet, why are you storing a computed value anyhow? Why not just use a
SELECT statement or a view and compute the value on the fly?
Rick Sawtell|||A computed column or one being used in a computed column cannot be altered,
nor can a computed column be updated.
What are you traing to do? If you need to change the behaviour of a computed
column, you need to drop it first. You cannot add a non-nullable column
without specifying a default value.
However, you can create a temporary table to store the values of the
computed column, drop the column, add a new column (it needs to be nullable)
,
then fill it with previous values, and alter it to make it non-nullable.
And if you post DDL and sample data, we can help you build a script to
achieve all this.
ML|||Thank you both. I think I realized I was overthinking this one a bit. It
doesn't have to be computed. I put MsgID on as varchar(64) NOT NULL, update
d
it for existing data w/this:
UPDATE tableA...
SET MsgID = endpoint+(convert(varchar(8),[exectime],
112) + [ordernumber])
GO
Then I changed it to NOT NULL and created the unique constraint. All
w/existing data in the table. What do you guys think?
-- Lynn
"ML" wrote:

> A computed column or one being used in a computed column cannot be altered
,
> nor can a computed column be updated.
> What are you traing to do? If you need to change the behaviour of a comput
ed
> column, you need to drop it first. You cannot add a non-nullable column
> without specifying a default value.
> However, you can create a temporary table to store the values of the
> computed column, drop the column, add a new column (it needs to be nullabl
e),
> then fill it with previous values, and alter it to make it non-nullable.
> And if you post DDL and sample data, we can help you build a script to
> achieve all this.
>
> ML|||Does it work as it is supposed to work? :)
Looks like you've nailed it.
ML|||I don't know yet, it's still running now. I am sure hoping we're good on
this one...
-- Lynn
"ML" wrote:

> Does it work as it is supposed to work? :)
> Looks like you've nailed it.
>
> ML|||Worked beautifully. Thank you guys for looking into this w/me.
-- Lynn
"ML" wrote:

> Does it work as it is supposed to work? :)
> Looks like you've nailed it.
>
> ML|||On Wed, 7 Sep 2005 12:01:02 -0700, Lynn wrote:

>Thank you both. I think I realized I was overthinking this one a bit. It
>doesn't have to be computed. I put MsgID on as varchar(64) NOT NULL, updat
ed
>it for existing data w/this:
>UPDATE tableA...
>SET MsgID = endpoint+(convert(varchar(8),[exectime],
112) + [ordernumber])
>GO
>Then I changed it to NOT NULL and created the unique constraint. All
>w/existing data in the table. What do you guys think?
Hi Lynn,
If the MsgID column will always be the concatenation of these three
other columns, than I wouldn't store it like this. Create a view that
does the concatenation if you prefer the ease of use.
For enforcing uniqueness, just do
ALTER TABLE tableA
ADD CONSTRAINT MyUniq UNIQUE (endpoint, exectime, ordernumber)
No need to add an extra column for this.
Of course, if the MsgID for NEW rows in the database will be filled with
other values, and this concatenation is just the "starting" value for
existing rows, then the above doesn't apply.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Indexing that view might also be of help - that way the concatenated values
are stored as if the view were a table. Views aren't cached permanently.
However, as Hugo already stated, this only applies if the values need to be
generated by concatenation *every time*.
MLsqlsql