SQL Query --Help

Question

Post reply

SQL Query --Help

bharathi-903755

SSC-Addicted

Points: 425
More actions
November 24, 2008 at 10:30 pm

#196811

Hi,
SELECT
C.RCN_ID As CandidateID,
I.RIV_ID As InterviewID
FROM
Abacus_Rec_INTERVIEW AS I
INNER JOIN
(SELECT
RCN_ID
FROM
Abacus_Rec_Candidate
GROUP BY
RCN_ID) C
ON
I.RIV_RCN_ID = C.RCN_ID
WHERE RCN_ID = 126542
This gives :
CandidateID InterviewID
126542 77288
126542 28484
126542 12671
126542 67246
126542 47597
But,I wanted to display it in 1 row,
CandidateID InterviewID
126542 77288,28484,12671,..
Can this be done???

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

rosh-396756 SSCrazy Points: 2338 More actions · Answer 1

see if this article helps

http://www.sqlservercentral.com/articles/Test+Data/61572/

krayknot SSCertifiable Points: 7998 More actions · Answer 2

bharathi (11/24/2008)
Hi,
SELECT
C.RCN_ID As CandidateID,
I.RIV_ID As InterviewID
FROM
Abacus_Rec_INTERVIEW AS I
INNER JOIN
(SELECT
RCN_ID
FROM
Abacus_Rec_Candidate
GROUP BY
RCN_ID) C
ON
I.RIV_RCN_ID = C.RCN_ID
WHERE RCN_ID = 126542
This gives :
CandidateID InterviewID
126542 77288
126542 28484
126542 12671
126542 67246
126542 47597
But,I wanted to display it in 1 row,
CandidateID InterviewID
126542 77288,28484,12671,..
Can this be done???

Yes this type of output is possible. you want the output in sql or in some third party tool like crystal report

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 3

above link is good for your query, it may effect performance issue for large volume of data.

winash SSChampion Points: 11446 More actions · Answer 4

Also check http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html for options on how to use FOR XML PATH to concatenate...

bharathimc02 Mr or Mrs. 500 Points: 520 More actions · Answer 5

shithij,

I want this output in SQL Server Business Intelligence Studio...

krayknot SSCertifiable Points: 7998 More actions · Answer 6

bharathimc02 (11/24/2008)
shithij,
I want this output in SQL Server Business Intelligence Studio...

DECLARE @MyResult TABLE (PK Int, SumOfPockets Varchar(4000))

DECLARE @pk varchar(1000)

DECLARE @MyString Varchar(4000)

SET @MyString = ''

DECLARE cMyTable CURSOR LOCAL FAST_FORWARD FOR

SELECT Convert(varchar,datecreated) FROM dummy Where value = 2 GROUP BY datecreated

OPEN cMyTable

FETCH NEXT FROM cMyTable INTO @pk

WHILE @@FETCH_STATUS=0

BEGIN

SET @MyString = @MyString + @pk + ','

FETCH NEXT FROM cMyTable INTO @pk

END

CLOSE cMyTable

DEALLOCATE cMyTable

INSERT INTO @MyResult(PK,SumOfPockets) VALUES (2, @MyString)

SELECT * FROM @MyResult

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

winash SSChampion Points: 11446 More actions · Answer 7

krayknot - the cursor solution you've provided is not the best approach. For most situations we don't need to use a cursor.

In the links that were provided are good examples of how to get concatenated data without using cursors - including some detailed performance analysis of concatenating functions and how to get the best out of them...

krayknot SSCertifiable Points: 7998 More actions · Answer 8

winash (11/25/2008)
krayknot - the cursor solution you've provided is not the best approach. For most situations we don't need to use a cursor.
In the links that were provided are good examples of how to get concatenated data without using cursors - including some detailed performance analysis of concatenating functions and how to get the best out of them...

If there are URLs then what is the use of sqlservercentral.com, rather than in wasting time in searching from the links, there should be a readymade solution available for the less time consumption.

and regarding cursors, use while loop instead of cursors, if u dont want

kshitij kumar
kshitij@krayknot.com
www.krayknot.com