|
Dear All,
I am looking to query followings in Sql 2005 table as
Table A
ID Name
1 A
1 B
1 C
2 D
2 E
2 F
I want the result as
ID Name
1 A,B,C
2 D,E,F
any ideas
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
You have to use cursor. check this.[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Thanks. I have used for Xml Path and I have done it in SQl 2005.
but i want to do it only with pure T-Sql which I need the same thing in Ms Access also.
any ideas for Ms Access 2003/2007
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Following one of the solution work for you but there must be better solution than this
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION tableaValue
(
@id int
)
RETURNS varchar(50)
AS
BEGIN
declare @name varchar(50)
declare @tempString varchar(50)
DECLARE tablea_cursor CURSOR FOR
select name from tablea where id = @id -- Variable value from the outer cursor
OPEN tablea_cursor
FETCH NEXT FROM tablea_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @tempString= isnull(@tempString,'') + ',' + @name
FETCH NEXT FROM tablea_cursor INTO @name
END
CLOSE tablea_cursor
DEALLOCATE tablea_cursor
-- Return the result of the function
RETURN @tempString
END
GO
select dbo.tableaValue(id),id from tablea group by id
|
|
|
|
|
You dont even need the cursor to build a CSV, you can use COALESCE
DECLARE @nameCsv VARCHAR(MAX)
SET @nameCSV = ''
SELECT @nameCsv = COALESCE(name + ',','') + @nameCsv
FROM tablea
SELECT LEFT(@nameCsv,LEN(@nameCsv)-1) AS NameCsv
|
|
|
|
|
its good but it nned it id wise see the question he need csv value which group by id
i hope this will on id basis
thanks for reply
|
|
|
|
|
I have no idea what you just said 
|
|
|
|
|
how abt this ?
select b.id, b.name from TableA b
where b.id in (select distinct a.id from TableA a) Apurv
“Never trust a computer you can’t throw out a window.”
(Steve Wozniak)
“There are only two industries that refer to their customers as ‘users’.”
(Edward Tufte)
|
|
|
|
|
Thanks for reply.
the query returns my table as it is, not the output which I am searching forAbdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Oh .. sry ..
I think 'cursors' can only solve this ...Apurv
“Never trust a computer you can’t throw out a window.”
(Steve Wozniak)
“There are only two industries that refer to their customers as ‘users’.”
(Edward Tufte)
|
|
|
|
|
Hi all, How to create temp table with new column name from a existing table that has different
column name.
thanks
modified on Wednesday, February 3, 2010 8:19 AM
|
|
|
|
|
Easy
select column1 as newname
into #temp
from oldtable
where 1 = 2
insert into #temp(newname)
select column1
from oldtable
By creating an empty table you avoid locking a lot of the system tables in tempdb - ALL the time a select into is running the system tables in tempdb are locked, which prevents other users from creating temp tables.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I will have to try it then I will let you know.
thanks for your help
|
|
|
|
|
Jashimu--you have been a member long enough to know not to use such a general message title. Please refresh yourself with the forum guidelines if needed!
|
|
|
|
|
Hi,
How can I get all children in just one level downer than a parent in hiererchy in SQL Server 2008?
Best wishes
|
|
|
|
|
Typical example, organ area problem. The table name is organ:
ID OrganName ParentID
1 A 0
2 a 1
3 b 1
SELECT a.* from organ as a, organ as b where a.ParentID=B.ID
The result is:
ID OrganName ParentID
2 a 1
3 b 1
April
Comm100 - Leading Live Chat Software Provider
modified 27-May-14 8:49am.
|
|
|
|
|
For some reason I can't add my dataconnect. I click on add connection select sql server
then I browse to the programfiles/...sqlserver/data and click on books.mdf
it tells me every single time that the file is in use. But I don't know of any program that would be using it. I'm running the server on my desktop simply to try and learn it and do a little programming with it. i can't seem to understand why it will not access that database.
Any help would be great thanks...
|
|
|
|
|
With ProcMon[^] you can investigate which process has the file in use.
Wout Louwers
|
|
|
|
|
At a guess the database has a connection that is still live, it may be left over from debugging or a corrupt connection.
Also Access may be corrupt and refusing the connection, try repair and rebuild via the Access UI.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Is there a new file in the directory holding your database, specifically a lock file? I'm new to SQL Server, but in Access a file is created when a connection is established that locks out other users. Sometimes when connections are not correctly closed, the file defining the lock is not deleted, blocking any subsequent access attempts. Deleting the locking file eliminates the problem. Is there an equivalent locking file in SQL Server? If so, deleting it may restore your access to the database.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I finally got it resolved. I actually had the server manager running connected to the sql server. So I took and closed that and refreshed the datasource and worked fine. thanks for the help all
|
|
|
|
|
Hi,
I need to handle missing select values programattically. this is what i have
insert into A(id, name, age, value, DOB)
select 1,'Max','21','a','100928' union
select 2,'x','100948' union
select 3,'Rex','32','b','100928' union
select 4,'xx','100938' union
select 5 'Fed','24','xx','100928'
all my insert statement are stripped off via regex. the structure of insert statement is as show above. but at times it may miss "name" and "age" and it gives me error something like
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions
now those insert scripts are quite bulky. sometime 100MB I would like to automatically handle missing name and age. I am not sure how could i do that? could you please give me some idea?
|
|
|
|
|
You could pass Null as the value if you don't know the name or age.
Wout Louwers
|
|
|
|
|
how do i pass null value without modifying below block of query
select 1,'Max','21','a','100928' union
select 2,'x','100948' union
select 3,'Rex','32','b','100928' union
select 4,'xx','100938' union
select 5 'Fed','24','xx','100928'
Lets just assume above text is a read only text.
|
|
|
|
|
If you don't change the query, the insert will not work. The query should look like this:
select 1,'Max','21','a','100928' union
select 2, Null, Null,'x','100948' union
select 3,'Rex','32','b','100928' union
select 4, Null, Null,'xx','100938' union
select 5 'Fed','24','xx','100928'
Wout Louwers
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
