August 21, 2012 at 10:36 am
Hi All,
My output looks like this
100
200
300
400
500
But I want to convert above output to
'100,200,300,400,500,'
For this how to write the query???
Regards,
VenkiDesai.
August 21, 2012 at 12:31 pm
DECLARE @aa table(col varchar(20))
insert into @aa
values('100'),('200'),('300'),('400'),('500')
SELECT col+',' FROM @aa FOR XML PATH('')
August 21, 2012 at 3:26 pm
Expanding on mohankollu's solution... You can get this returned as text in the result set with
DECLARE @aa table(col varchar(20))
insert into @aa values('100'),('200'),('300'),('400'),('500');
WITH t(col) AS
(
SELECT col+','
FROM @aa FOR XML PATH('')
)
SELECT col FROM t
-- Itzik Ben-Gan 2001
August 22, 2012 at 2:13 am
Thanks for all replying to my post
NOW IAM CLEARLY EXPLAING MY TASK,
SELECT DISTINCT ID FROM TABLE1
When i execute the above statement
The Output comes like this,
ID
----
100
200
300
400
500
so now i want to insert all the above ID's into My Second Table
INSERT INTO TABLE2(ID) VALUES(@ID)
I WROTE LIKE THIS
DECLARE @Sample VARCHAR(MAX)
SET @Sample=(SELECT DISTINCT ID FROM TABLE1)
INSERT INTO TABLE2(ID)VALUES(@Sample)
BUT IT IS SHOWING ERROR LIKE THIS,
(Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.)
SO,HOW TO WRITE QUERY FOR THIS?????
August 22, 2012 at 2:34 am
venkidesaik (8/22/2012)
Thanks for all replying to my postNOW IAM CLEARLY EXPLAING MY TASK,
SELECT DISTINCT ID FROM TABLE1
When i execute the above statement
The Output comes like this,
ID
----
100
200
300
400
500
so now i want to insert all the above ID's into My Second Table
INSERT INTO TABLE2(ID) VALUES(@ID)
I WROTE LIKE THIS
DECLARE @Sample VARCHAR(MAX)
SET @Sample=(SELECT DISTINCT ID FROM TABLE1)
INSERT INTO TABLE2(ID)VALUES(@Sample)
BUT IT IS SHOWING ERROR LIKE THIS,
(Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.)
SO,HOW TO WRITE QUERY FOR THIS?????
When you write in caps lock, it makes you look like you're shouting. This isn't the best way to ask for help from people that are volunteering their time to help you solve a problem that you couldn't solve on your own. What's worse is that you've actually already been given a 90% coded solution, and a link that would help you to work this out on your own.
Here's a solution.
INSERT INTO TABLE2(ID)
SELECT STUFF((SELECT ',' + CAST(ID AS VARCHAR(5))
FROM TABLE1
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'');
Here's another.
DECLARE @Sample VARCHAR(MAX);
SELECT @Sample = STUFF((SELECT ',' + CAST(ID AS VARCHAR(5))
FROM TABLE1
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'');
INSERT INTO TABLE2(ID)
SELECT @Sample;
August 22, 2012 at 2:52 am
Thanks for replying Cadavre.
Sorry for writing my post in caps.
Actually i don't know the meaning that if i post a query in caps it is like shouting.
From now onwards while posting a query i don't write in caps.
Once again thanks for replying & my required output is getting.
August 22, 2012 at 4:01 am
venkidesaik (8/22/2012)
Thanks for all replying to my postNOW IAM CLEARLY EXPLAING MY TASK,
SELECT DISTINCT ID FROM TABLE1
When i execute the above statement
The Output comes like this,
ID
----
100
200
300
400
500
so now i want to insert all the above ID's into My Second Table
INSERT INTO TABLE2(ID) VALUES(@ID)
I WROTE LIKE THIS
DECLARE @Sample VARCHAR(MAX)
SET @Sample=(SELECT DISTINCT ID FROM TABLE1)
INSERT INTO TABLE2(ID)VALUES(@Sample)
BUT IT IS SHOWING ERROR LIKE THIS,
(Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.)
SO,HOW TO WRITE QUERY FOR THIS?????
If your TABLE2 structure is as you have described, you don't need to do any string concatenation. It's straight forward like that:
INSERT INTO TABLE2(ID)
SELECT DISTINCT ID FROM TABLE1
August 22, 2012 at 5:45 am
CREATE TABLE tblKit
(
KitId INT,
TargetId INT,
QId INT
)
This is my actual table structure & i have some records
(SELECT DISTINCT TargetId FROM tblKit) If I run this Query the output will be like this
TargetId
--------
100
200
300
400
500
(SELECT DISTINCT QId FROM tblKit) If I run this Query the output will be like this
QId
---
101
102
103
104
105
I want to take those results & i want to insert into the table[tblKit] my requirement is like that.
& my expected out is like this,
KitId Targetid QId
------ -------- ------
1 100 101
1 200 102
1 300 103
1 400 104
1 500 105
Is it possible to get Output Like This according to above Distinct results
For this i wrote,
DECLARE @KitId INT;
DECLARE @TargetId VARCHAR(MAX);
DECLARE @QId VARCHAR(MAX);
SET @KitId=1-->(I Passed this Value From Front-End)
SET @TargetId=(SELECT DISTINCT TargetId FROM tblKit)
SET @QId=(SELECT DISTINCT QId FROM tblKit)
INSERT INTO tblKit (KitId,TargetId,QId ) VALUES (@KitId,@TargetId,@QId)
I know that the above one is wrong,Here multiple records are getting for both @TargetId & @QId
Atleast i want to try na thats why i am showing what i wrote
Would you please give the stored procedure for above requirements?
From front-end i only pass KitId i wont pass TargetId & QId.
August 22, 2012 at 6:49 am
Can you be a bit more accurate in explaining your problem. You did show the same table as source and destination. Is it really what you want? Is tblKit is source or destination
What exact data you have in your "source" table What results the query
SELECT * FROM [YourSourceTable]
will produce (if the ?
Can you please provide the setup as per: http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 22, 2012 at 11:11 pm
You can do it as follows:
--Creating Tables
Create Table Ex
(TargetId int )
Create Table Ex1
(QID int )
Create Table Ex2
(KitId int,
Targetid int,
QId int )
--Inserting Sample Data
Insert into Ex
Select 100
Union ALL
Select 200
Union ALL
Select 300
Union ALL
Select 400
Union ALL
Select 500
Insert Into Ex1
Select 101
Union ALL
Select 102
Union ALL
Select 103
Union ALL
Select 104
Union ALL
Select 105
--Query for your Requirement
Insert Into Ex2
Select 1, a.TargetId, b.QID From
(
Select *, ROW_NUMBER() Over (Order By TargetId) As rn From Ex
) As a
JOIN
(
Select *, ROW_NUMBER() Over (Order By QID) As rn From Ex1
) As b
ON a.rn = b.rn
Just add RowNumber() to your Select Distinct Queries and Join them as Derived Tables and Insert the result Set into the required table as shown above. Hope this helps.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply