|
|
||||||
|
#1
10-26-06, 02:21 PM
|
|
|
|
|
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 |
|
|
|
#2
10-26-06, 03:17 PM
|
|
|
|
|
SELECT ID, SPACE(ID * 3) + DataField
FROM YourTable |
|
#3
10-26-06, 03:31 PM
|
|
|
|
|
That is not what I want....Thanks
"Roji P Thomas" wrote: [..] |
|
#4
10-26-06, 04:12 PM
|
|
|
|
|
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 |
|
#5
10-26-06, 04:43 PM
|
|
|
|
|
> 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/ |
|
#6
10-26-06, 05:24 PM
|
|
|
|
|
help please
"rpresser" wrote: [..] |
|
#7
10-26-06, 05:45 PM
|
|
|
|
|
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 |
|
#8
10-26-06, 05:46 PM
|
|
|
|
|
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 | |
| Thread | Thread Starter |
| 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. |
Waldy |
| 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... |
Waldy |
| 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... |
kevin |
| Query Problem - Query works in Query window but not as code in a f This Query will work as QUERY6 in the form and in the query window, but when I take the code and put it into the record source of a form I get a syntax error in from clause. ... |
PatT123 |
| 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... |
David A. Beck |
|
Privacy Policy | All times are GMT. The time now is 06:09 PM.
|
|
query