TSQL – Concatenare righe utilizzando FOR XML PATH()

For example, if you have the following data:

– © 2011 – Vishal (http://SqlAndMe.com)

USE AdventureWorks2008R2

SELECT      CAT.Name AS [Category],
            SUB.Name AS [Sub Category]
FROM        Production.ProductCategory CAT
INNER JOIN  Production.ProductSubcategory SUB
            ON CAT.ProductCategoryID = SUB.ProductCategoryID

The desired output here is to concatenate the subcategories in a single row as:

We can achieve this by using FOR XML PATH(), the above query needs to be modified to concatenate the rows:

– © 2011 – Vishal (http://SqlAndMe.com)
USE AdventureWorks2008R2

SELECT      CAT.Name AS [Category],
            STUFF((    SELECT ',' + SUB.Name AS [text()]
                        – Add a comma (,) before each value
                        FROM Production.ProductSubcategory SUB
                        WHERE
                        SUB.ProductCategoryID = CAT.ProductCategoryID
                        FOR XML PATH('') – Select it as XML
                        ), 1, 1, '' )
                        – This is done to remove the first character (,)
                        – from the result
            AS [Sub Categories]
FROM  Production.ProductCategory CAT

Executing this query will generate the required concatenated values as depicted in above screen shot.

Eseguire ciclicamente una Stored Procedure leggendo i paramentri da una Select.

Declare @TableUsers Table (User_ID, MyRowCount Int Identity(1,1)
Declare @i Int, @MaxI Int, @UserID nVarchar(50)

Insert into @TableUser
Select User_ID
From Users 
Where (My Criteria)
Select @MaxI = @@RowCount, @i = 1

While @i <= @MaxI
Begin
Select @UserID = UserID from @TableUsers Where MyRowCount = @i
Exec prMyStoredProc @UserID
Select

 @i = @i + 1, @UserID = null
End

Conversione di una data in formato stringa in datetime.

// Conversione di una data in formato stringa in datetime.
public class ConvStringToDateTime
{
    public DateTime data { get; set; }
    public Boolean esitoConversioneOk { get; set; }


    public ConvStringToDateTime(string dataDaVerificare)
    {
        CultureInfo culturaItaliana = new CultureInfo("it-IT");
        DateTime dataVerificata;
        if (!(DateTime.TryParse(dataDaVerificare, culturaItaliana, DateTimeStyles.AssumeLocal, out dataVerificata)))
        {
            // Data non valida
            dataVerificata = new DateTime(1900, 1, 1);
            esitoConversioneOk = false;
        }
        else
        {
            // Data valida
            esitoConversioneOk = true;
        }
        data = dataVerificata;
    }
}

// Utilizzo
ConvStringToDateTime dataRichiestaIntervento = new ConvStringToDateTime(Row.SorgenteDataRichiestaIntervento);
if (!dataRichiestaIntervento.esitoConversioneOk)
{
    messaggio = messaggio + ", Data Richiesta Intervento";
    Row.dataRichiestaIntervento = dataRichiestaIntervento.data;
}
else
    Row.dataRichiestaIntervento = dataRichiestaIntervento.data;