如何在一行中获取所有值 [英] How to get all values in a single row

问题描述

执行它之后在我的下面sp ..作为EXEC sp_SplitVal

Hi,
In my below sp after executing it..as EXEC sp_SplitVal

ALTER PROC sp_SplitVal
AS
DECLARE @Test VARCHAR(1000), @RecSet VARCHAR(500)
DECLARE @StartingPos INT,@RecPos INT
DECLARE @temptable TABLE(COMPLEXIONID INT)
SET @StartingPos= 1
SET @TEST='1,3,4,'
--SET @Test =(select cmp.complexion1 from customerexpectation cmp where cmp.customerid='4fe6559c-bc93-4d9c-bcb2-013fede7d969')
WHILE 
    @StartingPos<=LEN(@Test)
BEGIN
    SELECT @RecPos = CHARINDEX(',',@Test,@StartingPos)
    SET @RecSet=SUBSTRING(@Test,@StartingPos,@RecPos-@StartingPos)
    PRINT @RecSet
    BEGIN
        INSERT INTO @temptable VALUES(@RecSet)
        SELECT * FROM @temptable
        SELECT @StartingPos=@RecPos+1
    END
END

i我将结果循环三次..

i am getting the results in a loop for three times..as

COMPLEXIONID
     1
COMPLEXIONID
     1
     3
COMPLEXIONID
     1
     3
     4

但我需要它在单个记录集中..

but i need it as in a single recordset..

COMPLEXIONID
     1
     3
     4

i不想循环...如何实现这一目标...

i dont want in a loop...how to achieve this...

推荐答案

嗨Sahmed，

你的Sql Query是好的，你只需要在外循环中使用select Query

Hi Sahmed,
your Sql Query is Ok, you just need to use select Query outside loop

alter PROC sp_SplitVal
AS
BEGIN
DECLARE @Test VARCHAR(1000), @RecSet VARCHAR(500)
DECLARE @StartingPos INT,@RecPos INT
DECLARE @temptable TABLE(COMPLEXIONID INT)
SET @StartingPos= 1
SET @TEST='1,3,4,'
--SET @Test =(select cmp.complexion1 from customerexpectation cmp where cmp.customerid='4fe6559c-bc93-4d9c-bcb2-013fede7d969')
WHILE @StartingPos<=LEN(@Test)
BEGIN
SELECT @RecPos = CHARINDEX(',',@Test,@StartingPos)
SET @RecSet=SUBSTRING(@Test,@StartingPos,@RecPos-@StartingPos)
PRINT @RecSet
BEGIN
INSERT INTO @temptable VALUES(@RecSet)
SELECT @StartingPos=@RecPos+1
END
END
SELECT * FROM @temptable
end

谢谢！

Yogendra Dubey

Thanks!
Yogendra Dubey

你只有一组数据，你的问题是你用来获取数据的选择是在循环中，执行：

You only have one set of data, your problem is that the select you use to get the data is in the loop, execute this:

DECLARE @Test VARCHAR(1000), @RecSet VARCHAR(500)
DECLARE @StartingPos INT,@RecPos INT
DECLARE @temptable TABLE(COMPLEXIONID INT)
SET @StartingPos= 1
SET @TEST='1,3,4,'
--SET @Test =(select cmp.complexion1 from customerexpectation cmp where cmp.customerid='4fe6559c-bc93-4d9c-bcb2-013fede7d969')
WHILE 
    @StartingPos<=LEN(@Test)
BEGIN
    SELECT @RecPos = CHARINDEX(',',@Test,@StartingPos)
    SET @RecSet=SUBSTRING(@Test,@StartingPos,@RecPos-@StartingPos)
    PRINT @RecSet
    BEGIN
        INSERT INTO @temptable VALUES(@RecSet)
        SELECT @StartingPos=@RecPos+1
    END
END
SELECT * FROM @temptable

我已将选择移到最后一行。

I''ve moved the select to the last line.

这篇关于如何在一行中获取所有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！