How to concatenate an SQL Server statement?

Asked

Viewed 66 times

0

I have a table in SQL Server Management Studio in which I have the fields test | 01 | 02 | 03 | 04 | 05 | 06 | 07 | 08 Create a for loop in Visual Studio’s C# in another table that counted how many times a given Y note came out after the X note, and this calculation took more than 18 minutes, so I created a whole statement in SQL and the calculation was reduced to 50 seconds. I tried to migrate the instruction format to this table above, but since the columns have 2 decimal places (01, 02, 03 .. 08) I cannot implement the sql statement for this table in question. The instruction in question would be:

DECLARE @nota1 INT
DECLARE @nota2 INT
DECLARE @posicao1 INT
DECLARE @posicao2 INT

SET @nota1 = 1
SET @nota2 = 2
SET @posicao1 = 1
SET @posicao2 = 2

WHILE @nota2 <= 20
BEGIN
    WHILE @nota1 <= 20
    BEGIN
        WHILE @posicao1 <= 7
        BEGIN
            WHILE @posicao2 <= 8
            BEGIN
                INSERT INTO tabela2 ([nota1], [posicao1], [nota2], [posicao2], [qtde]) VALUES
                    (
                    @nota1,
                    @posicao1,
                    @nota2,
                    @posicao2,
                    (SELECT COUNT(1) AS 'Valor' 
                FROM tabela
                WHERE '[0' + @posicao1 + ']' = @nota1 AND '[' + @posicao2 + ']' = @nota2))
            SET @posicao2 += 1
            END
        SET @posicao1 += 1
        END
    SET @nota1 += 1
    END 
SET
 @nota2 += 1
END

The SELECT instruction to work would have to be the same as in the example: 

SELECT COUNT(1) AS 'Valor' FROM tabela WHERE [01] = @nota1 AND [02] = @nota2

How do I add the while instruction to two decimal places? Or how to concatenate?

sql
  • 1

    I didn’t quite understand the question, but to enter two decimal places you can use select campo + 0.00 as meuCampoFloat from tabela

  • I created the While instruction to scroll through columns from 01 to 08. In the sql statement it would have to be for example: SELECT * FROM table WHERE [01] = 5 but the while statement will not put between [ ] nor put with 2 house, [01] and when adding with SET @posicao1 += 1 goes out of 1 and goes to 2 and not [02]

1 answer

0


After much research I found the final function

DECLARE @nota1 INT
DECLARE @nota2 INT
DECLARE @posicao1 INT
DECLARE @posicao2 INT

SET @nota2 = 2
WHILE @nota2 <= 20
BEGIN
    SET @nota1 = 1
    WHILE @nota1 <= 20
    BEGIN
        SET @posicao1 = 1
        WHILE @posicao1 <= 7
        BEGIN
            SET @posicao2 = 2
            WHILE @posicao2 <= 8
            BEGIN
                INSERT INTO tabela2 ([nota1], [posicao1], [nota2], [posicao2], [qtde]) VALUES
                    (
                    @nota1,
                    @posicao1,
                    @nota2,
                    @posicao2,
                    (SELECT COUNT(1) AS 'Valor' 
                FROM tabela
                WHERE 
                    CASE
                        WHEN @posicao1 = 1 THEN [01]
                        WHEN @posicao1 = 2 THEN [02]
                        WHEN @posicao1 = 3 THEN [03]
                        WHEN @posicao1 = 4 THEN [04]
                        WHEN @posicao1 = 5 THEN [05]
                        WHEN @posicao1 = 6 THEN [06]
                        WHEN @posicao1 = 7 THEN [07]
                    END
                = @nota1 AND 
                    CASE
                        WHEN @posicao1 = 2 THEN [02]
                        WHEN @posicao1 = 3 THEN [03]
                        WHEN @posicao1 = 4 THEN [04]
                        WHEN @posicao1 = 5 THEN [05]
                        WHEN @posicao1 = 6 THEN [06]
                        WHEN @posicao1 = 7 THEN [07]
                        WHEN @posicao1 = 8 THEN [08]
                    END
                = @nota2))
            SET @posicao2 = @posicao2 + 1
            END
        SET @posicao1 = @posicao1 + 1
        END
    SET @nota1 = @nota1 + 1
    END 
SET
 @nota2 = @nota2 + 1
END

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.