Concatenate Multiple Rows?

I can't figure out how to write an SQL query that concatenates one field from a list of records having other parameters in common. It is easier to show than explain. Take the data set:

Spec T_R Section
A008 23w 1
A008 23w 2
A008 23w 4

I need a query that returns a single record/row like this:

Spec T_R Section
A008 23w 1, 2, 4

Any help would be appreciated.I've had this problem more times than I can count. While I was writing my SQL Tutorial (http://www.bitesizeinc.net/index.php/sql.html), I ran across this function for MySQL :

group_concat(field)

Which concatenates the grouped results into a string. If you are using Oracle, you'll need a stored procedure...

-Chrissqlsql

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.

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

Composite Key?

I'm just now learning both SQL and ASP.NET, and I cannot seem to figure out how to build my data structure. I believe the answer to my problem is a composite key, but I cannot seem to get it to work. Here is an example. My database is of recorded dances, with exact locations within a ballroom. I believe I need 2 tables

Table #1 - DanceTable
Columns: DanceID, Name, Description, Tags

Table #2 - StepsTable
Columns DanceID, StepID, longLocation, latLocation, Action, Description

Within my ASP.NET application I want to be able to enter data about a dance, including metadata and a series of steps. The Dance and metadata content to be stored in DanceTable, and the series of moves stored in the StepsTable. I want the steps to be IDed as 1, 2, 3, 4...x with the first step being labled 1. and I want each dance to have it's own unique ID (DanceID). Right now I'm using "ExecuteNonQuery()" to add my data to my SQL database, and when I add new steps to the StepsTable SQL just finds the largest ID within StepID and increments it by one. So my steps are labeled as:

Dance1:
Step1, Step2, Step3, Step4

Dance2:
Step5, Step 6, Step7

What I really want is (or I think what I want is) is a composite primary key.

Dance1:
Step1, Step2, Step3, Step4

Dance2:
Step1, Step2, Step3

That way the StepID is used as both a primary key and it indicates the position within the dance. I guess I could just use a standard SQL table, let SQL auto generate StepID's and then add a new column called something like "StepNumber", but it just seems goofy to be generating a stepID and never using it. With composite keys (If I understand them) each step would have a unique key as a combination of the DanceID+StepID (AKA Dance 345, steps 1-10).

I pull up data by searching for dances, and then sort by StepNumber, and those should all be unique...if I can figure out how to build them.

A composite key is just a key made from multiple fields. In your case, it would be DanceID,StepID.

Your tables look fine, although if you make the StepID an identity field, you really don't need to store the "StepNumber". It's redundant. You can derive the "StepNumber" by the number of records that have the same DanceID and a lower StepID.

This would get you the @.StepNumber-th step:

SELECT TOP 1 *

FROM ( SELECT TOP (@.StepNumber) *

FROM StepsTable

WHEREDanceID=@.DanceID

ORDER BY StepID ASC) t1

ORDER BY t1.StepID DESC

Or get them all in order with "StepNumber":

SELECT *,ROW_NUMBER() OVER (ORDER BY StepID) As StepNumber

FROM StepsTable

WHEREDanceID=@.DanceID

ORDER BY StepID

But of course there is nothing prohibiting you from including a StepNumber field (Like in case you don't always want the steps to be renumbered, or they aren't contiguious, like step 1, 3, 5 with no step 2 or 4, etc). In this case, your primary key would be StepID, and I would create a unique index/constraint on DanceID,StepNumber.

Component icons

Hi,

I'm trying to add an icon to a custom component but its not happening and I can't figure out why. I'm sure I've managed to do this in the past. Here's my DtsPipelineComponent attribute:

Code Snippet

namespace Jamiet.Ssis
{
[DtsPipelineComponent(
DisplayName="MyComponent",
Description="Does some stuff",
ComponentType=ComponentType.Transform,
NoEditor=true,
IconResource="MyComponent.ico"
)

MyComponent.ico is stored in the same folder as my project (i.e. at the same level as the .cs file and the C# project file).

In the application property page I've pointed the icon resource at MyComponent.ico

Everything compiles OK. But I see no icon in my component when I deploy it to the toolbox or use it in a package.

I've also tried changing the attribute to this:

Code Snippet

namespace Jamiet.Ssis
{
[DtsPipelineComponent(
DisplayName="MyComponent",
Description="Does some stuff",
ComponentType=ComponentType.Transform,
NoEditor=true,
IconResource="Jamiet.Ssis.MyComponent.ico"
)

Again, it compiles OK, but no icon!

Any ideas what I'm doing wrong?

Thanks

Jamie

Having in no way shape or form done this myself, looking at pg 476 of the wrox professional guide it appears what you are doing is correct (the only difference I see is the closing bracket "]" but I don't believe that is the problem)...

[DtsPipelineComponent(

DisplayName="ReverseString",

ComponentType="ComponentType.Transform,

IconResource="Konesans.Dts.Pipeline.ReverseString.ReverseString.ico",

UITypeName="Konesans.Dts.Pipeline.ReverseStringUI.ReverseStringUI, Konesans.Dts.Pipeline.ReverseStringUI, Version=1.1.0.0, Culture = neutral, PublicKeyToken=7b20fe705a17bed2")]

public class ReverseString : PipelineComponent

...

|||

In the project properties for the .ico file, make sure you have Build Action set to Embedded Resource.

Also, you may need to use a fully qualified name for the icon - the full assemby name.[your icon].ico

|||

cheers John. I'll give it a bash tonight.

Bothers me that there is no docs around this.

|||

jwelch wrote:

In the project properties for the .ico file, make sure you have Build Action set to Embedded Resource.

Also, you may need to use a fully qualified name for the icon - the full assemby name.[your icon].ico

Hey hey hey. It was the build action bit that I was missing.

Thanks John! Much appreciated!

Component icons

Hi,

I'm trying to add an icon to a custom component but its not happening and I can't figure out why. I'm sure I've managed to do this in the past. Here's my DtsPipelineComponent attribute:

Code Snippet

namespace Jamiet.Ssis
{
[DtsPipelineComponent(
DisplayName="MyComponent",
Description="Does some stuff",
ComponentType=ComponentType.Transform,
NoEditor=true,
IconResource="MyComponent.ico"
)

MyComponent.ico is stored in the same folder as my project (i.e. at the same level as the .cs file and the C# project file).

In the application property page I've pointed the icon resource at MyComponent.ico

Everything compiles OK. But I see no icon in my component when I deploy it to the toolbox or use it in a package.

I've also tried changing the attribute to this:

Code Snippet

namespace Jamiet.Ssis
{
[DtsPipelineComponent(
DisplayName="MyComponent",
Description="Does some stuff",
ComponentType=ComponentType.Transform,
NoEditor=true,
IconResource="Jamiet.Ssis.MyComponent.ico"
)

Again, it compiles OK, but no icon!

Any ideas what I'm doing wrong?

Thanks

Jamie

Having in no way shape or form done this myself, looking at pg 476 of the wrox professional guide it appears what you are doing is correct (the only difference I see is the closing bracket "]" but I don't believe that is the problem)...

[DtsPipelineComponent(

DisplayName="ReverseString",

ComponentType="ComponentType.Transform,

IconResource="Konesans.Dts.Pipeline.ReverseString.ReverseString.ico",

UITypeName="Konesans.Dts.Pipeline.ReverseStringUI.ReverseStringUI, Konesans.Dts.Pipeline.ReverseStringUI, Version=1.1.0.0, Culture = neutral, PublicKeyToken=7b20fe705a17bed2")]

public class ReverseString : PipelineComponent

...

|||

In the project properties for the .ico file, make sure you have Build Action set to Embedded Resource.

Also, you may need to use a fully qualified name for the icon - the full assemby name.[your icon].ico

|||

cheers John. I'll give it a bash tonight.

Bothers me that there is no docs around this.

|||

jwelch wrote:

In the project properties for the .ico file, make sure you have Build Action set to Embedded Resource.

Also, you may need to use a fully qualified name for the icon - the full assemby name.[your icon].ico

Hey hey hey. It was the build action bit that I was missing.

Thanks John! Much appreciated!

Compilation Errors When Trying To Compile Package Body!

Hi,

I am receiving compilation errors when I am trying to compile a package body, but for the life of me I can figure out why!

Can anybody help? Its driving me insane!!

--============================================
--= PACKAGE BODY
--============================================
CREATE OR REPLACE PACKAGE BODY student_manager
AS

PROCEDURE insert_student
(i_SALUTATION IN VARCHAR2,
i_first_name IN VARCHAR2,
i_last_name IN VARCHAR2,
i_STREET_ADDRESS IN VARCHAR2,
i_ZIP IN VARCHAR2,
i_PHONE IN VARCHAR2,
i_EMPLOYER IN vARcHAR2,
i_REGISTRATION_DATE IN DATE);
IS
BEGIN
INSERT INTO student VALUES
(get_new_student_id,
i_SALUTATION,
i_first_name,
i_last_name,
i_STREET_ADDRESS,
i_ZIP,
i_PHONE,
i_EMPLOYER,
i_REGISTRATION_DATE,
user,
sysdate,
user,
sysdate);
END insert_student;

--============================================

PROCEDURE delete_student
(i_student_id IN student.student_id%TYPE)
IS
BEGIN
DELETE FROM student
WHERE student.student_id = i_student_id;

END delete_student;

--============================================

FUNCTION get_new_student_id

RETURN NUMBER
IS
v_seq_id student.student_id%TYPE;

BEGIN
SELECT student_id_seq.nextval
INTO v_seq_id
FROM dual;
RETURN v_seq_id;

END get_new_student_id;

END student_manager;
.

--============================================
-- ERRORS:
--============================================

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY STUDENT_MANAGER:

LINE/COL ERROR
--- --------------------
13/1 PLS-00103: Encountered the symbol "IS" when expecting one of the
following:
begin end function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor

34/1 PLS-00103: Encountered the symbol "PROCEDURE"
59/1 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
begin function package pragma procedure formThere a just a couple of things I noticed, which you can try fixing-

1. Your statement- CREATE OR REPLACE PACKAGE BODY student_manager
AS
should read as-
CREATE OR REPLACE PACKAGE BODY student_manager is

2. You have a semicolon in the procedure insert_student before 'IS'
( i_REGISTRATION_DATE IN DATE);)
remove this semicolon and try.

--Shekhar Pendyala

Originally posted by iknownothing
Hi,

I am receiving compilation errors when I am trying to compile a package body, but for the life of me I can figure out why!

Can anybody help? Its driving me insane!!

--============================================
--= PACKAGE BODY
--============================================
CREATE OR REPLACE PACKAGE BODY student_manager
AS

PROCEDURE insert_student
(i_SALUTATION IN VARCHAR2,
i_first_name IN VARCHAR2,
i_last_name IN VARCHAR2,
i_STREET_ADDRESS IN VARCHAR2,
i_ZIP IN VARCHAR2,
i_PHONE IN VARCHAR2,
i_EMPLOYER IN vARcHAR2,
i_REGISTRATION_DATE IN DATE);
IS
BEGIN
INSERT INTO student VALUES
(get_new_student_id,
i_SALUTATION,
i_first_name,
i_last_name,
i_STREET_ADDRESS,
i_ZIP,
i_PHONE,
i_EMPLOYER,
i_REGISTRATION_DATE,
user,
sysdate,
user,
sysdate);
END insert_student;

--============================================

PROCEDURE delete_student
(i_student_id IN student.student_id%TYPE)
IS
BEGIN
DELETE FROM student
WHERE student.student_id = i_student_id;

END delete_student;

--============================================

FUNCTION get_new_student_id

RETURN NUMBER
IS
v_seq_id student.student_id%TYPE;

BEGIN
SELECT student_id_seq.nextval
INTO v_seq_id
FROM dual;
RETURN v_seq_id;

END get_new_student_id;

END student_manager;
.

--============================================
-- ERRORS:
--============================================

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY STUDENT_MANAGER:

LINE/COL ERROR
--- --------------------
13/1 PLS-00103: Encountered the symbol "IS" when expecting one of the
following:
begin end function package pragma procedure subtype type use
<an identifier> <a double-quoted delimited-identifier> form
current cursor

34/1 PLS-00103: Encountered the symbol "PROCEDURE"
59/1 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
begin function package pragma procedure form|||Hi,

You need to put a forward slash '/' at the end of the package specification. The slash needs to be on a line by itself after the last "end;" in the package specification.

Comparing two tables and spitting out an XLS

Hi,

I'm trying to figure out a way to compare two tables, table one has more
entries than table two, I want SQL to compare table one to table two and
spit out and XLS of the enries that exist in table one, but not in table
two.

So far I can't even get my query right...heh

select * from table1 a
left join tabl2 b on a.column=b.column
where a.column exists not b.column

am I missing an "in" in the select portion on my query?

thanks alot for any help.Your name (fake.email@.address.com) writes:

Quote:

Originally Posted by

I'm trying to figure out a way to compare two tables, table one has more
entries than table two, I want SQL to compare table one to table two and
spit out and XLS of the enries that exist in table one, but not in table
two.

SELECT a.*
FROM a
WHERE NOT EXISTS (SELECT *
FROM b
WHERE a.keycol = b.keycol)

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