Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure?
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.com
Aditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure?
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com
|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatenation, or expose a
computed column in the table. That way the data doesn't get out of sync in case someone modifies the
first name or last name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How will
> i do it?Is it possible with a Stored Procedure?
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com
|||Thank you very much Uri, but there is one problem, there are five null spaces
in between the fullname, how do i remove these?
"Uri Dimant" wrote:

> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>
|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:

> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concatenation, or expose a
> computed column in the table. That way the data doesn't get out of sync in case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>
|||I don't know what a "null space" is. Are you saying that you can have NULL in either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to go on (CREATE TABLE,
INSERT).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...[vbcol=seagreen]
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables
sqlsql

Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure'
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.comAditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatena
tion, or expose a
computed column in the table. That way the data doesn't get out of sync in c
ase someone modifies the
first name or last name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How wil
l
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Thank you very much Uri, but there is one problem, there are five null space
s
in between the fullname, how do i remove these?
"Uri Dimant" wrote:

> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:

> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concate
nation, or expose a
> computed column in the table. That way the data doesn't get out of sync in
case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>
>|||I don't know what a "null space" is. Are you saying that you can have NULL i
n either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to
go on (CREATE TABLE,
INSERT).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...[vbcol=seagreen]
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
>|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables

Combining Two Records ( STORED PROCEDURE )

I have a table with more than 6000 records.The table contains, FIRSTNAME,
LASTNAME, ADDRESS, TELEPHONE,...
Now i have added a new column in the table, naming it FULLNAME. In this i
want to insert the FIRSTNAME and the LASTNAME
combined with a space in between, the rest all remaining the same. How will
i do it?Is it possible with a Stored Procedure'
I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
You can also email me at : aditya595@.yahoo.comAditya
Create table #test
(
[id] int not null primary key,
firstname varchar(50)not null,
lastname varchar(50) not null
)
insert into #test values (1,'John', 'Smith')
insert into #test values (2,'Bill', 'Clinton')
alter table #test add fullname varchar(50) null
select * from #test
update #test set fullname =( select firstname +' '+ lastname
from #test t where t.[id]=#test.[id])
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How
> will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Assuming no nulls in firstname or lastname column:
UPDATE tbl
SET FULLNAME = FirstName + ' ' + LastName
But, why do you need to store this? You can create a view with the concatenation, or expose a
computed column in the table. That way the data doesn't get out of sync in case someone modifies the
first name or last name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>I have a table with more than 6000 records.The table contains, FIRSTNAME,
> LASTNAME, ADDRESS, TELEPHONE,...
> Now i have added a new column in the table, naming it FULLNAME. In this i
> want to insert the FIRSTNAME and the LASTNAME
> combined with a space in between, the rest all remaining the same. How will
> i do it?Is it possible with a Stored Procedure'
> I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> You can also email me at : aditya595@.yahoo.com|||Thank you very much Uri, but there is one problem, there are five null spaces
in between the fullname, how do i remove these?
"Uri Dimant" wrote:
> Aditya
> Create table #test
> (
> [id] int not null primary key,
> firstname varchar(50)not null,
> lastname varchar(50) not null
> )
> insert into #test values (1,'John', 'Smith')
> insert into #test values (2,'Bill', 'Clinton')
> alter table #test add fullname varchar(50) null
>
> select * from #test
> update #test set fullname =( select firstname +' '+ lastname
> from #test t where t.[id]=#test.[id])
>
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
> > LASTNAME, ADDRESS, TELEPHONE,...
> > Now i have added a new column in the table, naming it FULLNAME. In this i
> > want to insert the FIRSTNAME and the LASTNAME
> > combined with a space in between, the rest all remaining the same. How
> > will
> > i do it?Is it possible with a Stored Procedure'
> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> >
> > You can also email me at : aditya595@.yahoo.com
>
>|||Thank you very much Tibor, i never realised it was such a simple approach,
but still again there are five null spaces in between the fullname, i.e.
between the firstname & lastname.how do i remove it?
"Tibor Karaszi" wrote:
> Assuming no nulls in firstname or lastname column:
> UPDATE tbl
> SET FULLNAME = FirstName + ' ' + LastName
> But, why do you need to store this? You can create a view with the concatenation, or expose a
> computed column in the table. That way the data doesn't get out of sync in case someone modifies the
> first name or last name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
> > LASTNAME, ADDRESS, TELEPHONE,...
> > Now i have added a new column in the table, naming it FULLNAME. In this i
> > want to insert the FIRSTNAME and the LASTNAME
> > combined with a space in between, the rest all remaining the same. How will
> > i do it?Is it possible with a Stored Procedure'
> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
> >
> > You can also email me at : aditya595@.yahoo.com
>
>|||I don't know what a "null space" is. Are you saying that you can have NULL in either of the columns?
If so:
UPDATE tbl
SET FULLNAME = COALESCE(FirstName, '') + ' ' + COALESCE(LastName, '')
If you mean something else, please follow to give us a clear description to go on (CREATE TABLE,
INSERT).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aditya" <Aditya@.discussions.microsoft.com> wrote in message
news:35F18EEE-79F1-421C-B32E-09739A5D59F7@.microsoft.com...
> Thank you very much Tibor, i never realised it was such a simple approach,
> but still again there are five null spaces in between the fullname, i.e.
> between the firstname & lastname.how do i remove it?
> "Tibor Karaszi" wrote:
>> Assuming no nulls in firstname or lastname column:
>> UPDATE tbl
>> SET FULLNAME = FirstName + ' ' + LastName
>> But, why do you need to store this? You can create a view with the concatenation, or expose a
>> computed column in the table. That way the data doesn't get out of sync in case someone modifies
>> the
>> first name or last name.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Aditya" <Aditya@.discussions.microsoft.com> wrote in message
>> news:1EBC2659-F8F2-4FBD-A117-A04A7B98D516@.microsoft.com...
>> >I have a table with more than 6000 records.The table contains, FIRSTNAME,
>> > LASTNAME, ADDRESS, TELEPHONE,...
>> > Now i have added a new column in the table, naming it FULLNAME. In this i
>> > want to insert the FIRSTNAME and the LASTNAME
>> > combined with a space in between, the rest all remaining the same. How will
>> > i do it?Is it possible with a Stored Procedure'
>> > I am using SQL SERVER 2000 as my database. PLEASE HELP ME.
>> >
>> > You can also email me at : aditya595@.yahoo.com
>>|||Do you mean whitespaces? If so use:
Update tbl
SET fullname = LTRIM(RTRIM(Firstname)) + ' ' + LTRIM(RTRIM(Lastname))
but really, the firstname and lastname fields shouldn't have spaces in the
first place. Your data entry should remove them on insert/update, but to
remove the ones already done you could just do
Update tbl
set firstname = LTRIM(RTRIM(Firstname)), lastname = LTRIM(RTRIM(lastname))
and the original update statement should work then.
But like Tibor said, your best creating a view or computed column than to
add redundant information to your tables

Combining records/Foreach Loop

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

cboom wrote:

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

Won't the following work:

UPDATE h

SET h.DentalConcern = c.MaxDentalConcern,

h.VisionConcern = c.MaxVisionConcern,

c.HearingConcern = c.MaxHearingConcern

FROM Health h

INNER JOIN (

SELECT ChildID,

CAST(MAX(CAST(DentalConcern as tinyint)) AS bit) as MaxDentalConcern,

CAST(MAX(CAST(VisionConcern as tinyint)) AS bit) as MaxVisionConcern,

CAST(MAX(CAST(HearingConcern as tinyint)) AS bit) as MaxHearingConcern,

FROM concerns

GROUP BY ChildID

) c

ON h.ChildID = c.ChildID

?

-Jamie

|||

Well, back to basic Transact-SQL for me. Did play with doing Max on the boolean fields which obviously didn't work, and didn't even think to Cast to integer. Many, many thanks.

Chera

Combining records

Good day
I have a SQL results set that looks like this:
CandidateID Nationality
90509 SA
90509 UK
90509 IT
90509 FR
I need my results to look like this:
CandidateID Nationality
90509 SA, UK, IT, FR
Is this possible? What approach should I take to accomplish this?
Thanks and kind regards,
KarlKarl,
Better if you do it in your client app / reporting tool / programming
lenguage. There are a lot of cons to do it in t-sql. See if this helps.
http://groups-beta.google.com/group...
5bf366dd9e73e
AMB
"Karl Basson" wrote:

> Good day
> I have a SQL results set that looks like this:
> CandidateID Nationality
> 90509 SA
> 90509 UK
> 90509 IT
> 90509 FR
> I need my results to look like this:
> CandidateID Nationality
> 90509 SA, UK, IT, FR
> Is this possible? What approach should I take to accomplish this?
> Thanks and kind regards,
> Karl
>
>

Combining many records into 1

Using SQL 2000, how can you combine multiple records into 1?
The source data is varchar(255), the destination will be text. I need help
with the select statement.

example tables:
CREATE TABLE [NoteHeader] (
[NoteID] [int],
[CustomerID] [int] ,
[Desc1] [varchar] (255),
[Date] [datetime] ,
)
GO

CREATE TABLE [NoteDetail] (
[NoteId] [int],
[SeqNum] [int] NOT NULL ,
[Note1] [varchar] (255),
[Note2] [varchar] (255),
[Note3] [varchar] (255),
[Note4] [varchar] (255),
[Note5] [varchar] (255)
)
GO

Sample script joining tables:
SELECT *
FROM NoteHeader INNER JOIN
NoteDetail ON NoteHeader.NoteID = NoteDetail.NoteId

Sample results:
NoteID CustomerID Desc1 Date
Note1 Note2
....Note5
1111 987 Note Header Description 2007-07-15
Notes detail record 1 field 1 Notes detail record 1 field2 ....
1111 987 Note Header Description 2007-07-15
Notes detail record 2 field 1 Notes detail record 2 field 2

Desired results:
NoteID CustomerID Desc1 Date
CombinedNotes
1111 987 Note Header Description 2007-07-15
Notes detail record 1 field 1 +

Notes detail record 1 field2 +

Notes detail record 2 field 1 +

Notes detail record 2 field 2 +

through unlimited number of records up to 5
fields each

The NoteID field is the unique number. 1 record per NoteID in NoteHeader,
NoteDetail can have unlimited number of same NoteID (usually not more than
10)rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

Using SQL 2000, how can you combine multiple records into 1?
The source data is varchar(255), the destination will be text. I need
help with the select statement.

SQL Server MVP Anith Sen as 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.mspx|||rdraider (rdraider@.sbcglobal.net) writes:

Quote:

Originally Posted by

Thanks for the info. My problem is the resulting data will be too large
for varchar(8000). All these examples seem to use varchar(8000)
I need to convert to a text datatype. I can concat multiple varchar fields
from 1 record into text but the problem is how the source data is
structured.
The source data is from an app called 'Onyx' running SQL 6.5 (I'm naming
names !!). I upgraded the SQL 6.5 to SQL 2000. I don't hav SQL 2005.

I think you have two options:

1) Get SQL 2005.
2) Do it client-side.

I think you can do it on SQL 2000, but then you would have to run
a cursor, and use WRITETEXT and UPDATETEXT and it would be very very
painful. Please don't ask me to write the code for you, but if you
have problems with using WRITETEXT and UPDATETEXT, I can try to assist.

Quote:

Originally Posted by

I assume it was designed this way because SQL 6.5 largest data type was
varchar(255) ?

Yes, that is correct.

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

Combining information from multiple records into one

Hi,

I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.

My table looks like this:
ID - DayNr - Transportation - TransOrder - Route
25 - 1 - Car - 1 - Text A
25 - 1 - Train - 1 - Text B
25 - 1 - Train - 2 - Text C
25 - 7 - Train - 1 - Text D
25 - 7 - Train - 2 - Text E

I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:

Column 1 - Column 2
25/1/Car - Text A
25/1/Train - TextB;TextC
25/7/Train - TextD;TextE

I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. Ideas anyone?

Thanks.
RMG

P.S. ID is not my primary key and doesn't have to be uniqueYou can use a simple while loop to iterate over the record and insert the records into an output table. If the values for the combination of columns in your source set changes, then insert a new row into the table, and if the values are identical, then append the value from the other column to the corresponding column's value in the new table.

create a table to represent the output
select source data from the database

while (more records)
{

if the current column combination is different
to the previous one, insert a new row
into the output table.

if the current column combination is equal
to the previous one, append the value of
columnX to the corresponding value of the
current row in the output table.

move to next record;

}

display the output to the user, for to not do so,
would defeat the purpose of writing the function.

Send an email to your director of IT asking why
such a function was even requested ;)

Regards,|||This looks completely logically to me. But it would be very helpfull if you could translate some of this logic into SQL for me. Because that is where I have the problem, not so much the necessary steps I need to follow.

And I am afraid I have to ask the last question to myself. As I am the one who wants to concatenate the information and put it in another table. And the answer is quite simple: This way I don't have to copy and paste information from thousands of records by hand. ;)|||Three options.

Robert's way.
UDF to which you pass the data that uniquely identifies your output row and that returns string of delimited values.
If there are a finite (and manageable) number of values for Route then a CASE statement (note- this will give you n columns for Route where n is the number of unique values).|||I was going to suggest the third option and provide an example using the CASE statement, but I wasn't certain if the number of rows to be transformed, and thus the number of columns required to produce, would be of a small enough size to make this approach practical. I believe you would need to code one case statement per possible column. For this to work you will need to assign some kind of unique numbering to the rows, so that each case statement to represent a column, knows what row to extract. Also, you will need to collapse the rows after applying the concatenation of your new columns, populated by the CASE statement, in order to remove the gaps.

Taking these factors into consideration, I felt that the iterative approach I described earlier would provide you with the simplest and quickest implementation.

Though of course, they are only the considerations that I was able to ascertain from reading your post.|||Also, you will need to collapse the rows after applying the concatenation of your new columns, populated by the CASE statement, in order to remove the gaps.Nah - just use MAX() - I think the BoL 2000 CASE entry demonstrates exactly this. Thinking about it, I guess one might use PIVOT in 2005 in lieu of the CASE statements.|||Wouldn't you still need to collapse the rows after having projected the MAX() values of each column, for example by using the GROUP BY.|||Well yes.

SELECT col_1
, col_2
, something = MAX(CASE WHEN [route] = 'something' THEN [route] END)
, anotherThing = MAX(CASE WHEN [route] = 'anotherThing' THEN [route] END)
, andSoOn = MAX(CASE WHEN [route] = 'andSoOn' THEN [route] END)
FROM dbo.mytable
GROUP BY col_1
, col_2|||I didn't intend to be pedantic, I just thought the poster should be aware that the result set would need to be "flattened".|||Ah beg your pardon - I wasn't sure whether or not you were asking questions on your behalf or the OPs. I thought your range extended well beyond this :).|||Select Col1+col2 As Merged_col
From ...

?|||Select Col1+col2 As Merged_col
From ...

?That handles Column 1 in the OPs desired results but look more carefully at column 2 - that's the tricky bit :)|||I think you will be able to get the result set you want doing something like this.

CREATE PROC sp_TrickySelect
AS
DECLARE @.str varchar(8000), @.tot int, @.l int, @.search varchar(1000), @.select varchar(8000), @.count int, @.l2 int, @.str_part varchar(1000)

SELECT ([ID]+'/'[DayNr]+'/'+[Transportation]) AS [Column1], [TransOrder], [Route]
INTO ##tmpTbl1
FROM ....

SELECT DISTINCT([Column1])
INTO ##tmpTbl2
FROM ##tmpTbl1

SET @.tot = SELECT COUNT(*) FROM ##tmpTbl2
SET @.l = 1

WHILE (@.l<=@.tot)
BEGIN
SET @.search = SELECT TOP 1 [Column1] FROM ##tmpTbl2
SET @.count = SELECT COUNT([Route]) FROM ##tmpTbl1 WHERE [Column1]=@.search
SET @.l2 = 1
WHILE (@.l2<=@.count)
BEGIN
SELECT @.str_part = SELECT TOP 1 [Route] FROM ##tmpTbl1 WHERE [Column1]=@.search ORDER BY [TransOrder] ASC
SELECT @.str = @.str+' '+@.str_part
DELETE ##tmpTbl1 WHERE [Column1]=@.search AND [TransOrder]=(SELECT MIN [TransOrder] FROM ##tmpTbl1 WHERE [Column1]=@.search)
SET @.l2 = @.l2 +1
END
INSERT INTO tmpTbl ([Column1], [Column2]) VALUES (@.search,@.str)
DELETE FROM ##tmpTbl2 WHERE [Column1]=@.search
SET @.l = @.l+1
END

SELECT [Column1], [Column2] FROM tmpTbl

NB: I wrote this directly on this website and I didn't test it.

Hope it can helps you out.

Regards

Or Tho|||If you use the Code from my previous post you gonna have to create a table tmpTable at the begining of the proc and drop it at the end OR create it and TRUNCATE it at the begining of the proc...|||I am compelled to vehemently advise against using a solution similar to the one described above. Of course, these views are intended to discuss the merits of the solution, and in no way are to be interpreted as being a reference to the author.

Ortho,

While your approach may indeed work, in comparing it with other solutions available, namely those developed in SQL, it is unnecessarily complex and bloated.

Not only is the length of your solution a reason to outright dismiss it, but it also consumes a large number of resources and the use of programming constructs that are disproportionate to the complexity of the problem. These include temp tables, while loops, conditional constructs, string manipulation functions, and finally, individual SQL statements.

Your approach is almost exclusively procedural in nature and as such, should be considered only, and only when ,a more elegant and often performance friendly set based solution is not available. Often an experienced SQL Developer can develop a solution using many times fewer resources and lines of code than what would otherwise b developed by a procedural coder. This is not to say that one skill set is more valuable than the other, but instead it serves to highlight the difference present in the mindsets of these two developers, and how their differing perspectives are suited for specific kinds of problems.

Generally procedural coders find it immensely difficult to develop efficient code for the manipulation and retrieval of data, in other words, developing code that works with data. I know that myself, I find it often difficult to see the benefits in OO programming for anything related to data. It's just a different way of viewing a problem.

As you can see by comparing your solution to that posted earlier in this thread, the number of explicitly programmed steps is much greater in your solution. This can increase the risk of errors being introduced during development and maintenance of the code, which is obviously, is a risk that developers and managers should strive to minimize.

Finally, your solution ignores the intent of the family of languages (unfortunately I cannot remember the exact term at the time of writing), of which SQL is indeed widely known, in developing a level of abstraction between the intent of a function and the internal representation of how it will be performed. In other words, the goal of SQL and other similar languages, is to focus on expressing the problem in terms of what needs to be done and not so much on how to do it.

To apply these principles to the problem posted by the original poster, we can see that an SQL solution can be developed in only a fraction of the lines of code and with no explicit declarations of variables or inclusion of procedural programming constructs.

Regards,|||r123456, I know my solution isn't the best but it's the only way I found to get the result set he wants...

Anyways thanks for the advise!

Peace

Or|||As I said, the intent of the post was to make the original poster aware that in that instance, a procedural approach was not the optimal solution, and instead a set based solution would be more appropriate.

combining datasets

I have two queries, one that brings back data from a cube and another that
returns records from a db. Is there anyway inside reporting services to
combine the two data sets?Only through subreports.
"Jessica C" <jesscobbe@.hotmail.com> wrote in message
news:ucK4AE02FHA.3272@.TK2MSFTNGP09.phx.gbl...
> I have two queries, one that brings back data from a cube and another that
> returns records from a db. Is there anyway inside reporting services to
> combine the two data sets?
>

Combining 2 sql records on one detail line

Hi,
Is there a way to take 2 sql records from a data set and combine them
on one detail line. My data set looks like
sales lane store_no week year
9930.04 2 C196 50 2006
7276.24 3 C196 50 2006
In reporting services I want to have a table that shows store, lane2
sales, lane3 sales, week, year. When I do this now I get 2 detail rows
one that shows lane2 sales and another that shows lane 3 sales. I have
tried using a matrix which does a nice job of pivoting the data but
then throws off the way I want my report to be layed out. In Crystal
report I could do calculations and running totals behind the scenes
and then drop the result of that on the report the way I want it. How
can I do that with RS? As a side note I have tried using calculated
fields in RS but they crash my Visual Stuido if I do any kind of
calulation or IIF statement.
Any help is appreciatedHey you can do this pivoting using sql query itself, so that the result set
will look like the way you want
Amarnath
"mcgrawc@.checkers.com" wrote:
> Hi,
> Is there a way to take 2 sql records from a data set and combine them
> on one detail line. My data set looks like
> sales lane store_no week year
> 9930.04 2 C196 50 2006
> 7276.24 3 C196 50 2006
> In reporting services I want to have a table that shows store, lane2
> sales, lane3 sales, week, year. When I do this now I get 2 detail rows
> one that shows lane2 sales and another that shows lane 3 sales. I have
> tried using a matrix which does a nice job of pivoting the data but
> then throws off the way I want my report to be layed out. In Crystal
> report I could do calculations and running totals behind the scenes
> and then drop the result of that on the report the way I want it. How
> can I do that with RS? As a side note I have tried using calculated
> fields in RS but they crash my Visual Stuido if I do any kind of
> calulation or IIF statement.
> Any help is appreciated
>|||I realize that using SQL I could pivot the data and I have written
some code to do just that however I was hoping to find a way in
Reporting Services to do this just like I could in Crystal Reports. My
company wants to use RS to replace our crystal reports but I am
finding either through my lack of experience with RS that some of our
reports are beyond what RS can currently give. I personally love some
of the RS features but miss my the ease of my Crystal formulas,
running totals, etc. I have several other reports that I have stopped
working on temporarily that have this same problem and I really am not
looking to write code for all of them to pivot the data into something
RS can use. If anyone has a report solution I would love to hear it.
Chadwick|||Have you tried using the RuningValue function in RS?
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"chadwick" wrote:
> I realize that using SQL I could pivot the data and I have written
> some code to do just that however I was hoping to find a way in
> Reporting Services to do this just like I could in Crystal Reports. My
> company wants to use RS to replace our crystal reports but I am
> finding either through my lack of experience with RS that some of our
> reports are beyond what RS can currently give. I personally love some
> of the RS features but miss my the ease of my Crystal formulas,
> running totals, etc. I have several other reports that I have stopped
> working on temporarily that have this same problem and I really am not
> looking to write code for all of them to pivot the data into something
> RS can use. If anyone has a report solution I would love to hear it.
> Chadwick
>

Combining 2 records into 1

I have the follwing tables which contain error records and error reasons.
There are two erros entries for every error reason.
DDL
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[INPUT_ERRORS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[INPUT_ERRORS]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[INPUT_ERRORS_REASON]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[INPUT_ERRORS_REASON]
GO
CREATE TABLE [dbo].[INPUT_ERRORS] (
[Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ErrorItemErrorItemType] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[AUtoRef] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[INPUT_ERRORS_REASON] (
[Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[ErrorMessageMessageLine] [varchar] (255) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[AUtoRef] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO
SET NOCOUNT ON
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','ORIGINAL RECORD','19069',523)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','RETURNED RECORD','19069',524)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','7',537)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED RECORD','7',538)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','CONTRA',539)
INSERT INTO [INPUT_ERRORS]
([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED
RECORD',' BACS',540)SET NOCOUNT ON
SET NOCOUNT OFF
SET NOCOUNT ON
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217523','16781','19069','RECI
PIENT''S SORT CODE IS INVALID',23
6)
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','7','YOUR ACCOUNT DETAILS ARE INVALID',243)
INSERT INTO [INPUT_ERRORS_REASON]
([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','CONTRA','YOU
R
CONTRA ACCOUNT DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID',244)
SET NOCOUNT OFF
i would like to get this sresult returned for easy reading in a report
Licence ReportNumber ErrorItemErrorItemType1 ErrorItemreference1
ErrorItemErrorItemType2 ErrorItemreference2 ErrorMessageMessageLi
ne
'217523' '16781' 'ORIGINAL RECORD' '19069'
'RETURNED RECORD' '19069' 'RECIPIENT''S SORT CODE IS
INVALID'
'217993' '25194' 'ORIGINAL RECORD' '7'
'AMENDED RECORD' '7' 'YOUR ACCOUNT DETAILS ARE
INVALID'
'217993' '25194' 'ORIGINAL RECORD' 'CONTRA'
'AMENDED RECORD' ' BACS' 'CONTRA','YOUR CONTRA ACCOUNT
DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID'Peter Newman wrote:
> I have the follwing tables which contain error records and error reasons.
> There are two erros entries for every error reason.
> DDL
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[INPUT_ERRORS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[INPUT_ERRORS]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[INPUT_ERRORS_REASON]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[INPUT_ERRORS_REASON]
> GO
> CREATE TABLE [dbo].[INPUT_ERRORS] (
> [Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ErrorItemErrorItemType] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [AUtoRef] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[INPUT_ERRORS_REASON] (
> [Licence] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ReportNumber] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [ErrorItemreference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [ErrorMessageMessageLine] [varchar] (255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [AUtoRef] [int] IDENTITY (1, 1) NOT NULL
> ) ON [PRIMARY]
> GO
>
> SET NOCOUNT ON
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','ORIGINAL RECORD','19069',523)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217523','16781','RETURNED RECORD','19069',524)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','7',537)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED RECORD','7',538)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','ORIGINAL RECORD','CONTRA',539)
> INSERT INTO [INPUT_ERRORS]
> ([Licence],[ReportNumber],[ErrorItemErro
rItemType],[ErrorItemreference],[AUtoRef
])VALUES('217993','25194','AMENDED
> RECORD',' BACS',540)SET NOCOUNT ON
> SET NOCOUNT OFF
> SET NOCOUNT ON
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217523','16781','19069','RECI
PIENT''S SORT CODE IS INVALID',
236)
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','7','YOUR ACCOUNT DETAILS ARE INVALID',243)
> INSERT INTO [INPUT_ERRORS_REASON]
> ([Licence],[ReportNumber],[ErrorItemrefe
rence],[ErrorMessageMessageLine],[AUtoRe
f])VALUES('217993','25194','CONTRA','YOU
R
> CONTRA ACCOUNT DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID',244)
> SET NOCOUNT OFF
> i would like to get this sresult returned for easy reading in a report
> Licence ReportNumber ErrorItemErrorItemType1 ErrorItemreference1
> ErrorItemErrorItemType2 ErrorItemreference2 ErrorMessageMessage
Line
> '217523' '16781' 'ORIGINAL RECORD' '19069'
> 'RETURNED RECORD' '19069' 'RECIPIENT''S SORT CODE IS
> INVALID'
> '217993' '25194' 'ORIGINAL RECORD' '7'
> 'AMENDED RECORD' '7' 'YOUR ACCOUNT DETAILS ARE
> INVALID'
> '217993' '25194' 'ORIGINAL RECORD' 'CONTRA'
> 'AMENDED RECORD' ' BACS' 'CONTRA','YOUR CONTRA ACCOUN
T
> DETAILS (FIELDS A/B/C AND/OR E/F) ARE INVALID'
Thanks for including the DDL. Unfortunately neither table has any keys!
Including an IDENTITY column means we can probably guess that that is a
key but an IDENTITY may not be enough to solve your problem. IDENTITY
should not be the only key of a table but you haven't shown us any
other so we can only guess. Here's one possibility:
SELECT licence, reportnumber,
LEFT(err1,50) AS erroritemtype1, RIGHT(err1,20) AS erroritemref1,
LEFT(err2,50) AS erroritemtype2, RIGHT(err2,20) AS erroritemref2
FROM
(SELECT licence, reportnumber,
MIN(CAST(erroritemerroritemtype AS CHAR(50))
+CAST(erroritemreference AS CHAR(20))) AS err1,
MAX(CAST(erroritemerroritemtype AS CHAR(50))
+CAST(erroritemreference AS CHAR(20))) AS err2
FROM input_errors
GROUP BY licence, reportnumber) AS T ;
David Portas
SQL Server MVP
--

combine table data fields from two records into a record

Dear helper,

I have the question of T-SQL.

I have a table original is:

and want to use Sql to make it becomes:

Concrete_Grade Mix_Code RM_ID RM_Name RM_Value UnitType_ID RMTypeType_Name RMType_Name
10P/20 10P kfdn_100 KFDN-100 2.24 kg Set Retarding Admixture
10P/20 10P kfdn_100 KFDN-100 2.24 kg Water-reducing Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Set Retarding Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Set Retarding Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Set Retarding Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Set Retarding Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Set Retarding Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Set Retarding Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Set Retarding Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Set Retarding Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Set Retarding Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Water-reducing Admixture

It is better to make it becomes a view for table joining.

Concrete_Grade Mix_Code RM_ID RM_Name RM_Value UnitType_ID RMTypeType_Name RMType_Name
10P/20 10P kfdn_100 KFDN-100 2.24 kg Set Retarding, Water-reducing Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Set Retarding, Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Set Retarding, Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Set Retarding, Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Set Retarding, Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Set Retarding, Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Set Retarding, Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Set Retarding, Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Set Retarding, Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Set Retarding, Water-reducing Admixture

Regards,

Man Pak Hong, Dave

try this..

SELECT a.Concrete_Grade
, a.Mix_Code
, a.RM_ID
, a.RM_Name
, a.RM_Value
, a.UnitType_ID
, a.RMTypeType_Name
, b.RMType_Name
FROM YourTable a INNER JOIN
YourTable b ON a.Concrete_Grade = b.Concrete_Grade
AND a.Mix_Code = b.Mix_Code
AND a.RM_ID = b.RM_ID
AND a.RM_Name = b.RM_Name
AND a.RM_Value = b.RM_Value
AND a.UnitType_ID = b.UnitType_ID|||

So your point is creating view which holds the values [RMTypeType_Name]='Water-reducing', lets say vw_MyData_WaterReducing. Later you want to join this view with outher tables on your query.

One way it is good if you use INDEXED VIEW. You have to create a index on this new view. It will increase the performance well.

But if you try to use with out index (only the filtered query), it may decrease the performance. You may unknowingly use Self join on your query...

To know better abotu indexed view visit here ... http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

combine data from different records with same ID

I have a table contains comments. User scan create as many comments they wa
nt.
my job is to combine and rearrange all comments in order of dates and time.
acct date time Comments
-- -- -- ---
08 01/04/2001 170852 0Conveyed stips.
84 01/04/2001 173740 test!
84 01/04/2001 173812 test2!
02 01/04/2001 180502 spoke to mbr and nd
01 01/05/2001 115548 joint life
01 01/05/2001 115550 Please fund loan.
18 01/05/2001 185220 Sent
18 01/05/2001 185238 Sent completed application
Desired Result:
acct Comments
----
--
08 Conveyed stips. 01/04/2001: 170852
84 test! - Ford 01/04/2001: 173740 test2! 01/04/2001: 173812
02 spoke to mbr and nd 01/04/2001: 180502
01 joint life 01/05/2001: 115548 Please fund loan. 01/05/2001: 1155
50
18 Sent 01/05/2001: 185220 Sent completed application 01/05/2001:
185238
Thanks in Advance,
CulamUse a document management system (textbase)and not SQL system.|||You haven't stated what datatypes these columns are.
Do type conversions as required and use the concatenation operator ( + ) to
achieve the results you want. What seems to be the difficulty in doing so?
Anith|||I converted all the data to VARCHAR and using a operator (+) to combine data
,
but I need to roll up all records with same id into one record. That is
what I need help in.
"Anith Sen" wrote:

> You haven't stated what datatypes these columns are.
> Do type conversions as required and use the concatenation operator ( + ) t
o
> achieve the results you want. What seems to be the difficulty in doing so?
> --
> Anith
>
>|||I see. This does not seem to be a right job for SQL Server. One good
approach to such problems is to retrieve the resultset and leverage the
string concatenation and loop-like functionality of a client programming
language to create the result.
The approaches in SQL are all more or less complex and cumbersome. Some of
the such hacks can be found at:
http://groups.google.ca/groups?selm...FTNGP09.phx.gbl
Anith

Combine columns from Two SELECT Statements

I have a database that tracks billing and payment history records against a "relationship" record (the "relationship" maps a many-to-many relationship between employees and cell phone numbers).

I have two statements that look like this:

SELECT CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed
FROM Relationship
INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN BillingHistory ON Relationship.PKRelationship = BillingHistory.FKRelationship
GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber

SELECT CellPhone.PhoneNumber, SUM(PaymentHistory.AmountPaid) AS TotalPaid
FROM Relationship
INNER JOIN CellPhone ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN PaymentHistoryON Relationship.PKRelationship = PaymentHistory.FKRelationship
GROUP BY Relationship.PKRelationship, CellPhone.PhoneNumber

Each statement correctly aggregates the sums, but I need a record that shows me:

CellPhone.PhoneNumber, SUM(BillingHistory.AmountOwed) AS TotalOwed, SUM(PaymentHistory.AmountPaid) AS TotalPaid

I can't figure out how to join or merge the statements together to get all of this information into one record without ruining the sums (I can't seem to correctly join the PaymentHistory table to the BillingHistory table without the sums going haywire).

Any help is appreciated.

Use each query as a derived table.

select

coalesce(a.PhoneNumber, b.PhoneNumber) as PhoneNumber,

a.TotalOwed,

b.TotalPaid

from

(

query A

) as a

full join

(

query B

) as b

on a.PhoneNumber = b.PhoneNumber

AMB

|||

You could try this. It might be less efficient, but you never know.

select

cellPhone.PhoneNumber,

(select sum(BillingHistory.AmountOwed)

from RelationShip

join BillingHistory

on Relationship.PKRelationship = BillingHistory.FKRelationship

where CellPhone.PKCellPhone= Relationship.FKCellPhone) as TotalOwed,

(select sum(PaymentHistory.AmountPaid)

from Relationship

join PaymentHistory

on Relationship.PKRelationship = PaymentHistory.FKRelationship

where CellPhone.PKCelPhone = Relatinship.FKCellPhone) as TotalPaid

from CellPhone

I'm not sure I see where GROUP BY Relationship.PKRelationship helps you here, but it could be needed somewhere.

Steve Kass

Drew University

www.stevekass.com

|||Why not just do something like this?

Code Snippet

SELECT CellPhone.PhoneNumber, ISNULL(SUM(BillingHistory.AmountOwed), 0) AS TotalOwed, ISNULL(SUM(PaymentHistory.AmountPaid), 0) AS TotalPaid
FROM CellPhone
LEFT OUTER JOIN OwedRelationship
ON CellPhone.PKCellPhone = OwedRelationship.FKCellPhone
LEFT OUTER JOIN BillingHistory
ON OwedRelationship.PKRelationship = BillingHistory.FKRelationship
LEFT OUTER JOIN PaidRelationship
ON CellPhone.PKCellPhone = PaidRelationship.FKCellPhone
LEFT OUTER JOIN PaymentHistory
ON PaidRelationship.PKRelationship = PaymentHistory.FKRelationship
GROUP BY OwedRelationship.PKRelationship, PaidRelationship.PKRelationship, CellPhone.PhoneNumber

|||David,

If you join all the tables together this way, the "sums will go haywire," as noted in the original post. Each AmountOwed value will appear multiple times in the sum - once for each AmountPaid value for the same account - and vice versa, so the query will not produce the desired result.

SK
|||

Steve Kass wrote:

You could try this. It might be less efficient, but you never know.

select

cellPhone.PhoneNumber,

(select sum(BillingHistory.AmountOwed)

from RelationShip

join BillingHistory

on Relationship.PKRelationship = BillingHistory.FKRelationship

where CellPhone.PKCellPhone= Relationship.FKCellPhone) as TotalOwed,

(select sum(PaymentHistory.AmountPaid)

from Relationship

join PaymentHistory

on Relationship.PKRelationship = PaymentHistory.FKRelationship

where CellPhone.PKCelPhone = Relatinship.FKCellPhone) as TotalPaid

from CellPhone

I'm not sure I see where GROUP BY Relationship.PKRelationship helps you here, but it could be needed somewhere.

Steve Kass

Drew University

www.stevekass.com

This (correctly) sums up the totals by phone number, but I need them summed up by Relationship (a relationship between an employee and a phone number), to distinguish the different owners of a single cell phone number.
|||

Steve Kass wrote:

David,

If you join all the tables together this way, the "sums will go haywire," as noted in the original post. Each AmountOwed value will appear multiple times in the sum - once for each AmountPaid value for the same account - and vice versa, so the query will not produce the desired result.

SK

This is exactly what does happen when I try David's solution.
|||

hunchback wrote:

Use each query as a derived table.

select

coalesce(a.PhoneNumber, b.PhoneNumber) as PhoneNumber,

a.TotalOwed,

b.TotalPaid

from

(

query A

) as a

full join

(

query B

) as b

on a.PhoneNumber = b.PhoneNumber

AMB

This seems almost correct, because the result set contains all of the rows I need, but it contains a lot of extra ones too. with erroneous data.

For example, I may get set that looks like:

Phone1 Owed1 Paid1
Phone2 Owed2 Paid1
Phone2 Owed2 Paid2
Phone3 Owed2 Paid3
Phone3 Owed3 Paid3

etc... with the bold rows being correct. The "correct" rows are all over the result set so I can't just cut out every other row.

|||You should be able to adapt it to sum by whatever you want. For example, if you want it summed by PhoneNumber and Relationship, proceed as follows.

1. Write a query that produces all the groups you want data for

select -- no sums of money data yet
cellPhone.PhoneNumber,
Relationship.PKRelationship
from <whatever is needed>

Then add the sums - figure out just how to get the sum for a specific PhoneNumber and Relationship and that will basicaly be your subquery. You will need to match both phone number and relationship with the outer tables, not just phone number. The results should look like this in outline:

select
C.PhoneNumber,
R.PKRelationship,
(
select sum(AmountOwed)
from ...
where CellPhone.PhoneNumber = C.PhoneNumber
and Relationship.PKRelationship = R.PKRelationship
)
from Relationship as R
join CellPhone as C
on ...

SK
|||

Steve Kass wrote:

You should be able to adapt it to sum by whatever you want. For example, if you want it summed by PhoneNumber and Relationship, proceed as follows.

1. Write a query that produces all the groups you want data for

select -- no sums of money data yet
cellPhone.PhoneNumber,
Relationship.PKRelationship
from <whatever is needed>

Then add the sums - figure out just how to get the sum for a specific PhoneNumber and Relationship and that will basicaly be your subquery. You will need to match both phone number and relationship with the outer tables, not just phone number. The results should look like this in outline:

select
C.PhoneNumber,
R.PKRelationship,
(
select sum(AmountOwed)
from ...
where CellPhone.PhoneNumber = C.PhoneNumber
and Relationship.PKRelationship = R.PKRelationship
)
from Relationship as R
join CellPhone as C
on ...

SK

Steve, you and I are now best friends. As soon as I dropped the INNER JOINs from the sub queries and used WHERE clauses, your solution worked.

Thanks a million!
|||

Comming from Steve Kass, no doubt it will work. Did you try using the queries as derived tables?

Code Snippet

select

coalesce(a.PhoneNumber, b.PhoneNumber) as PhoneNumber,

coalesce(a.PKRelationship, b.PKRelationship) as PKRelationship,

a.TotalOwed,

b.TotalPaid

from

(

SELECT

Relationship.PKRelationship,

CellPhone.PhoneNumber,

SUM(BillingHistory.AmountOwed) AS TotalOwed
FROM

Relationship
INNER JOIN

CellPhone

ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN

BillingHistory

ON Relationship.PKRelationship = BillingHistory.FKRelationship
GROUP BY

Relationship.PKRelationship, CellPhone.PhoneNumber

) as a

full outer join

(
SELECT

Relationship.PKRelationship,

CellPhone.PhoneNumber,

SUM(PaymentHistory.AmountPaid) AS TotalPaid
FROM

Relationship
INNER JOIN

CellPhone

ON CellPhone.PKCellPhone = Relationship.FKCellPhone
INNER JOIN

PaymentHistory

ON Relationship.PKRelationship = PaymentHistory.FKRelationship
GROUP BY

Relationship.PKRelationship, CellPhone.PhoneNumber
) as b

on a.PKRelationship = b.PKRelationship

and a.PhoneNumber = b.PhoneNumber

AMB

|||Tested and this solution works, too!

Column Sorting

I want to be able to allow a user of a report to sort the records returnedc in a table control based on the column heading they select.

ie say the report returns a list of properties as row headings then a list of cost categories as column headings with cost values as the data. I want the user to be able to click on a column heading say a cost castegory of 'Cleaning' i then want the report to order the properties by 'Cleaning Value'

Is that possoble?
cheers.... anyone...

In the June 2005 CTP, there's a new feature called Interactive Sort that does precisely what you're talking about. To access it, open up the properties of the header's text box and navigate to the Interactive Sort tab of the dialog.|||Sorry whats June 2005 CTP is it to do with SQL Reporting Services?

Regards,
Geoff|||

CTP - Community Technology Preview. It's a post-Beta release of the new Reporting Services 2005 due out this year. More information at -> http://www.microsoft.com/sql/2005/productinfo/ctp.mspx

As far as i know, the interactive sort was not available previous to this version. I was using a previous beta release of the 2005 edition and it did not have this capability.

|||cheers for this i guess i will have to just wait!

Coloring the mx value in a record set

Hi,
I have a simple report that displays several records having 2 fields, date
field and Quantity field
I ma,aged to alternate the colors of line (RosntBrown and black) bu I need
also to display the max Qty in a color different of the other ones, how this
can be achieved
ThanksHello eliassal,
You could add an Expression in the backgroundColor properties of the
textbox like this:
=IIF(ReportItems!Quantity.Value=MAX(Fields!Quantity.Value),"Black","Transpar
ent")
If is the max Qty, the background will be black and other will be
Transparent.
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||So many thankls, it works like a charm. Now, how about displaying different
colors for Max and Min values for the same text box. can we use 2 expressions
at the same time on the same textbox containing thye field.
Thanks
"Wei Lu [MSFT]" wrote:
> Hello eliassal,
> You could add an Expression in the backgroundColor properties of the
> textbox like this:
>
> =IIF(ReportItems!Quantity.Value=MAX(Fields!Quantity.Value),"Black","Transpar
> ent")
> If is the max Qty, the background will be black and other will be
> Transparent.
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello eliassal,
Of course. You could do like this:
=IIF(ReportItems!Quantity.Value=MAX(Fields!Quantity.Value),"Black",IIF(Repor
tItems!Quantity.Value=min(fields!Quantity.Value),"Red","Transparent"))
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi, I was in vacation, I will check next week and let you know
Thanks
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>