query

10-26-06, 01:21 PM

hngo01

I have a table:

ID DataField
------------------
1 aaaa
1 bbbb
2 ccccc
3 dddd
3 eeee

I want query that return me like this when I pass in an ID:
ID DataField
-----------------
1 aaaa,bbbb
2 ccccc
3 dddd,eeee
Thanks

10-26-06, 02:17 PM

Roji P Thomas

SELECT ID, SPACE(ID * 3) + DataField
FROM YourTable

10-26-06, 02:31 PM

hngo01

That is not what I want....Thanks

"Roji P Thomas" wrote:
[..]

10-26-06, 03:12 PM

rpresser

hngo01 wrote:
> I have a table:
>
> ID DataField
> ------------------
> 1 aaaa
> 1 bbbb
> 2 ccccc
> 3 dddd
> 3 eeee
>
> I want query that return me like this when I pass in an ID:
> ID DataField
> -----------------
> 1 aaaa,bbbb
> 2 ccccc
> 3 dddd,eeee
> Thanks

Denormalizing is best done in the client, not the database. However:

If you happen to be using SQL Server 2005, this might be the ticket:
http://blogs.x2line.com/al/archive/2005/11/13/1319.aspx

If not, try this:
http://blogs.x2line.com/al/articles/214.aspx

10-26-06, 03:43 PM

Alex Kuznetsov

> Denormalizing is best done in the client, not the database. However:

I do not think normalization rules apply to result sets. Consider the
following:

SELECT Country.Name AS CountryName, Party.LastName FROM Country JOIN
Party ON Country.CountryCode = Party.CountryCode

If CountryName and LastName were stored in one and the same table, that
would be denormalization all right, but what is wrong with selects like
this?
Are you suggesting that we should join Country and Party on the client?

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

10-26-06, 04:24 PM

hngo01

help please

"rpresser" wrote:
[..]

10-26-06, 04:45 PM

Krishnakumar S

Hi
This problem is not possible to solve in a single SQL statement. That's
definite. But you can simulate that using a scalar function.
/*Create the function*/
CREATE FUNCTION udfGetCommaSeperatedColumn
(@id INT)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @str VARCHAR(1000)
SET @str = ''
SELECT
@str = @str + ',' + cast(DataField AS VARCHAR(10))
FROM
Table1
WHERE
ID = @id
RETURN SUBSTRING(@str, 2, LEN(@str) - 1)
END

Next execute the following SQL

SELECT DISTINCT ID, dbo.udfGetCommaSeperatedColumn(ID) AS DataField FROM
Table1

10-26-06, 04:46 PM

rpresser

Alex Kuznetsov wrote:
> > Denormalizing is best done in the client, not the database. However:
>
> I do not think normalization rules apply to result sets. Consider the
> following:
>
> SELECT Country.Name AS CountryName, Party.LastName FROM Country JOIN
> Party ON Country.CountryCode = Party.CountryCode
>
> If CountryName and LastName were stored in one and the same table, that
> would be denormalization all right, but what is wrong with selects like
> this?
> Are you suggesting that we should join Country and Party on the client?

No, not at all. I spoke unclearly. I was referring specifically to the
particular 1NF violation the OP wants to do, where a column in many
rows is turned into a single column separated by commas. It's almost
always a bad idea to store data like this - 1NF requires no repeating
groups - but it's often a good idea on reports that a human reads.

Similar Threads
The query processor ran out of stack space during query optimization. Please simplify the query. How can I delete some rows from a database when I get the above error? It is a simple delete from table where column = value type statement.
The query processor ran out of stack space during query optimization. Please simplify the query. Hi there, I get the error above when I try to step into a SQL Server 2005 stored procedure using BIDS. The procedure runs OK when you execute it. Does anyone know why this...
verfiy a query like Query Analyzer's parse query function I thought that the parse query feature in Query Analyzer simply wrapped your statement in a TRAN and then issued a ROLLBACK statement to prevent making real changes... but i...
Intra-query parallelism caused your server command (process ID #50) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1) We just switched to a machine using 2 Xeon 2.2GHz computers and 2GM RAM. Some of the querys that had run fine on the older 2 PentiumIII 512MB computer now fail with the...