Return dataset in one column

Hi there

I have the following two tables

mainprofile (profile varchar(20), description)
accprofile (profile varchar(20), acct_type int)

Sample data could be

mainprofile
------
prof1 | profile one
prof2 | profile two
prof3 | profile three

accprofile
-----

prof1 | 0
prof1 | 1
prof1 | 2
prof2 | 0

Now doing a join between these two tables would return multiple rows,
but I would like to know whether it would be possible to return
acct_type horizontally in a column of the result set, e.g.

prof1 | profile one | [0,1,2]
prof2 | profile two | [0]

I could probably manage this with cursors, but it would be very
resource intensive. Is there a better way?

Regards,
LouisFor a one time data display or if this is used by a single application or a
report, you should consider retrieving the resultset to the client side,
leverage the display/presentation language's string manipulative features
and appropriately format the data there.

If this is more of a general requirement and used by several applications,
in certain cases it may make some sense to do it at the server using t-SQL.
For some options see: http://www.projectdmx.com/tsql/rowconcatenate.aspx
--
Anith|||

Quote:

Originally Posted by

For some options see: http://www.projectdmx.com/tsql/rowconcatenate.aspx

Thanks. In the end I decided to stick with using a CURSOR

Regards,
Louis

Return all fields with the same name with function

Following sample function. This one works in query analyzer, as well as
works as stored procedure. Because of the nature of the program I need it to
be function and return all fields from all tables, BUT this one do not want
to compile because it has fileds with the same names in different tables.
How to fix it?
ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
RETURNS TABLE
AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
column names on different tables to be displayed
FROM Table1 INNER JOIN (((Table5 AS uu
INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =
Table4.Filed5
WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/Tamir,
Do not use "*", instead specify all column names and if two column have the
same name then you have to use an alias.
SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
AMB
"Tamir Khason" wrote:

> Following sample function. This one works in query analyzer, as well as
> works as stored procedure. Because of the nature of the program I need it
to
> be function and return all fields from all tables, BUT this one do not wan
t
> to compile because it has fileds with the same names in different tables.
> How to fix it?
> ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
> RETURNS TABLE
> AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
> column names on different tables to be displayed
> FROM Table1 INNER JOIN (((Table5 AS uu
> INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
> INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
> INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =
> Table4.Filed5
> WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
>
> --
> Tamir Khason
> "The computer is no better
> than its program." [Elting Elmore Morison]
> http://www.dotnet.us/
>
>|||Thank you for response, BUT there are 10 tables with about 100 columns each
one. It's seemed me rather supid to specified 1000 columns for such
function.
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B37C0B0D-4876-4CB1-A80A-6F9A15972FB4@.microsoft.com...[vbcol=seagreen]
> Tamir,
> Do not use "*", instead specify all column names and if two column have
> the
> same name then you have to use an alias.
> SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
>
> AMB
> "Tamir Khason" wrote:
>|||Tamir Khason wrote:
> Thank you for response, BUT there are 10 tables with about 100
> columns each one. It's seemed me rather supid to specified 1000
> columns for such function.
>
I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at
best obfuscates what columns really need to be in the query and at worst
breaks compilation. For example, had there been no overlap in the
columns, your function would have compiled. If you then added a column
to one of the tables that caused an overlap, the next time the function
was altered, it would fail to compile. It would also not pick up the
column change even in a case of no overlap.
Also, most queries do not require all column values be returned.
Especially when there is more than one table and joins on common columns
are involved. It just adds SQL Server overhead.
To help script out long lists of columns, you can use QA and copy a
SELECT statement for a table to the clipboard.
David Gugick
Imceda Software
www.imceda.com|||That's fine, but in this case I need all columns (this is for DWH
applicaiton) so I'm still in trouble with large number of columns in table
the function returns
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OlbcdLBTFHA.3040@.TK2MSFTNGP10.phx.gbl...
> Tamir Khason wrote:
> I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at best
> obfuscates what columns really need to be in the query and at worst breaks
> compilation. For example, had there been no overlap in the columns, your
> function would have compiled. If you then added a column to one of the
> tables that caused an overlap, the next time the function was altered, it
> would fail to compile. It would also not pick up the column change even in
> a case of no overlap.
> Also, most queries do not require all column values be returned.
> Especially when there is more than one table and joins on common columns
> are involved. It just adds SQL Server overhead.
> To help script out long lists of columns, you can use QA and copy a SELECT
> statement for a table to the clipboard.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Return all fields with the same name with function

Following sample function. This one works in query analyzer, as well as
works as stored procedure. Because of the nature of the program I need it to
be function and return all fields from all tables, BUT this one do not want
to compile because it has fileds with the same names in different tables.
How to fix it?
ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
RETURNS TABLE
AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
column names on different tables to be displayed
FROM Table1 INNER JOIN (((Table5 AS uu
INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 = Table4.Filed5
WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
--
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/Tamir,
Do not use "*", instead specify all column names and if two column have the
same name then you have to use an alias.
SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
AMB
"Tamir Khason" wrote:
> Following sample function. This one works in query analyzer, as well as
> works as stored procedure. Because of the nature of the program I need it to
> be function and return all fields from all tables, BUT this one do not want
> to compile because it has fileds with the same names in different tables.
> How to fix it?
> ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
> RETURNS TABLE
> AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
> column names on different tables to be displayed
> FROM Table1 INNER JOIN (((Table5 AS uu
> INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
> INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
> INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 => Table4.Filed5
> WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
>
> --
> Tamir Khason
> "The computer is no better
> than its program." [Elting Elmore Morison]
> http://www.dotnet.us/
>
>|||Thank you for response, BUT there are 10 tables with about 100 columns each
one. It's seemed me rather supid to specified 1000 columns for such
function.
--
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B37C0B0D-4876-4CB1-A80A-6F9A15972FB4@.microsoft.com...
> Tamir,
> Do not use "*", instead specify all column names and if two column have
> the
> same name then you have to use an alias.
> SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
>
> AMB
> "Tamir Khason" wrote:
>> Following sample function. This one works in query analyzer, as well as
>> works as stored procedure. Because of the nature of the program I need it
>> to
>> be function and return all fields from all tables, BUT this one do not
>> want
>> to compile because it has fileds with the same names in different tables.
>> How to fix it?
>> ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
>> RETURNS TABLE
>> AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
>> column names on different tables to be displayed
>> FROM Table1 INNER JOIN (((Table5 AS uu
>> INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
>> INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
>> INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =>> Table4.Filed5
>> WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
>>
>> --
>> Tamir Khason
>> "The computer is no better
>> than its program." [Elting Elmore Morison]
>> http://www.dotnet.us/
>>|||Tamir Khason wrote:
> Thank you for response, BUT there are 10 tables with about 100
> columns each one. It's seemed me rather supid to specified 1000
> columns for such function.
>
I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at
best obfuscates what columns really need to be in the query and at worst
breaks compilation. For example, had there been no overlap in the
columns, your function would have compiled. If you then added a column
to one of the tables that caused an overlap, the next time the function
was altered, it would fail to compile. It would also not pick up the
column change even in a case of no overlap.
Also, most queries do not require all column values be returned.
Especially when there is more than one table and joins on common columns
are involved. It just adds SQL Server overhead.
To help script out long lists of columns, you can use QA and copy a
SELECT statement for a table to the clipboard.
David Gugick
Imceda Software
www.imceda.com|||That's fine, but in this case I need all columns (this is for DWH
applicaiton) so I'm still in trouble with large number of columns in table
the function returns
--
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OlbcdLBTFHA.3040@.TK2MSFTNGP10.phx.gbl...
> Tamir Khason wrote:
>> Thank you for response, BUT there are 10 tables with about 100
>> columns each one. It's seemed me rather supid to specified 1000
>> columns for such function.
>>
> I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at best
> obfuscates what columns really need to be in the query and at worst breaks
> compilation. For example, had there been no overlap in the columns, your
> function would have compiled. If you then added a column to one of the
> tables that caused an overlap, the next time the function was altered, it
> would fail to compile. It would also not pick up the column change even in
> a case of no overlap.
> Also, most queries do not require all column values be returned.
> Especially when there is more than one table and joins on common columns
> are involved. It just adds SQL Server overhead.
> To help script out long lists of columns, you can use QA and copy a SELECT
> statement for a table to the clipboard.
>
> --
> David Gugick
> Imceda Software
> www.imceda.comsql

Return all fields with the same name with function

Following sample function. This one works in query analyzer, as well as
works as stored procedure. Because of the nature of the program I need it to
be function and return all fields from all tables, BUT this one do not want
to compile because it has fileds with the same names in different tables.
How to fix it?
ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
RETURNS TABLE
AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
column names on different tables to be displayed
FROM Table1 INNER JOIN (((Table5 AS uu
INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =
Table4.Filed5
WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
Tamir,
Do not use "*", instead specify all column names and if two column have the
same name then you have to use an alias.
SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
AMB
"Tamir Khason" wrote:

> Following sample function. This one works in query analyzer, as well as
> works as stored procedure. Because of the nature of the program I need it to
> be function and return all fields from all tables, BUT this one do not want
> to compile because it has fileds with the same names in different tables.
> How to fix it?
> ALTER FUNCTION dbo.GetWhatever (@.MyEver int)
> RETURNS TABLE
> AS RETURN (SELECT Table4.Field1 AS Field, * <- HERE THE PROBLEM. Same
> column names on different tables to be displayed
> FROM Table1 INNER JOIN (((Table5 AS uu
> INNER JOIN Table2 ON uu.Filed2 = Table2.Filed2)
> INNER JOIN Table3 ON uu.Filed3 = Table3.Filed3)
> INNER JOIN Table4 ON uu.Filed4 = Table4.Filed4) ON Table2.Field5 =
> Table4.Filed5
> WHERE (((uu.Ever)=@.MyEver ) AND ((Table1.Filed999)=0)))
>
> --
> Tamir Khason
> "The computer is no better
> than its program." [Elting Elmore Morison]
> http://www.dotnet.us/
>
>
|||Thank you for response, BUT there are 10 tables with about 100 columns each
one. It's seemed me rather supid to specified 1000 columns for such
function.
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:B37C0B0D-4876-4CB1-A80A-6F9A15972FB4@.microsoft.com...[vbcol=seagreen]
> Tamir,
> Do not use "*", instead specify all column names and if two column have
> the
> same name then you have to use an alias.
> SELECT t1.c1, ..., t1.cn, t2.c1 as t2_c1, ..., t2.cn as t2_cn, ...
>
> AMB
> "Tamir Khason" wrote:
|||Tamir Khason wrote:
> Thank you for response, BUT there are 10 tables with about 100
> columns each one. It's seemed me rather supid to specified 1000
> columns for such function.
>
I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at
best obfuscates what columns really need to be in the query and at worst
breaks compilation. For example, had there been no overlap in the
columns, your function would have compiled. If you then added a column
to one of the tables that caused an overlap, the next time the function
was altered, it would fail to compile. It would also not pick up the
column change even in a case of no overlap.
Also, most queries do not require all column values be returned.
Especially when there is more than one table and joins on common columns
are involved. It just adds SQL Server overhead.
To help script out long lists of columns, you can use QA and copy a
SELECT statement for a table to the clipboard.
David Gugick
Imceda Software
www.imceda.com
|||That's fine, but in this case I need all columns (this is for DWH
applicaiton) so I'm still in trouble with large number of columns in table
the function returns
Tamir Khason
"The computer is no better
than its program." [Elting Elmore Morison]
http://www.dotnet.us/
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OlbcdLBTFHA.3040@.TK2MSFTNGP10.phx.gbl...
> Tamir Khason wrote:
> I agree with Alejandro. Do not use SELECT *. It's a shortcut that, at best
> obfuscates what columns really need to be in the query and at worst breaks
> compilation. For example, had there been no overlap in the columns, your
> function would have compiled. If you then added a column to one of the
> tables that caused an overlap, the next time the function was altered, it
> would fail to compile. It would also not pick up the column change even in
> a case of no overlap.
> Also, most queries do not require all column values be returned.
> Especially when there is more than one table and joins on common columns
> are involved. It just adds SQL Server overhead.
> To help script out long lists of columns, you can use QA and copy a SELECT
> statement for a table to the clipboard.
>
> --
> David Gugick
> Imceda Software
> www.imceda.com

Retrieving GUID from INSERT query -- HELP

I have tried many code sample but I am very stuck

The primary key of my database (SQL server 2005) table is a uniqueidentifier.

I am using the following code to insert a row into my table:

myCommand.CommandText = sqlEvent.ToString(); //add the sql query to the command
myCommand.Connection = this.dbConnection; //add the database connection to the command
myCommand.ExecuteNonQuery(); //execute the insert query

I need to retrieve the GUID that is automatically generated when the insert command is executed.

Can someone help me? How do I get the GUID that is automatically generated? I have tried lots of things like using

string _id = (string)myCommand.ExecuteScalar();

and I am still stuck. I will really appreciate it if someone can refer me to some code sample.

HELP

Here is a sample:

CREATE TABLE T1(
id uniqueidentifier PRIMARY KEY DEFAULT(NEWID()),
name nvarchar(100)
);

INSERT INTO T1 (name) OUTPUT inserted.id VALUES('name 1');

SELECT * from T1;

So you just need to call T-SQL similar to "INSERT INTO T1 (name) OUTPUT inserted.id VALUES('name 1');" with ExecuteScalar();

Thanks,

Zuomin