As a SQL Server user, you may come across situations where you need to concatenate and manipulate data from multiple records into a single record. In this blog post, we will discuss how to achieve this using SQL Server.

Let’s consider a scenario where we have a table called TSGItems with three attributes: OrderYear, SeqNumber, and Item. We want to concatenate the Item values for each unique combination of OrderYear and SeqNumber, while also applying some additional manipulations to the data.

Here is an example of the data in the TSGItems table:

Based on the requirements mentioned by Anthony, we need to:

1. Order the items alphabetically.
2. Insert an “and” before the last concatenation when there are more than one Item for each OrderYear and SeqNumber.
3. Insert a comma between each concatenation when there are three or more Items for each OrderYear and SeqNumber, except between the last two concatenations.
4. Insert the manipulated data into a temporary table called #TSGItems.

To achieve this, we can use a combination of temporary tables, cursors, and loops. Here is an example code that accomplishes the desired result:

CREATE TABLE #TGSITEMS (
  ORDERYEAR DATETIME,
  SEQNUMBER INT,
  ITEM VARCHAR(MAX)
)

INSERT INTO #TGSITEMS (ORDERYEAR, SEQNUMBER, ITEM)
SELECT '2008', 10001, 'Senior IRS Lien' UNION ALL
SELECT '2008', 10001, 'IRS Lien' UNION ALL
SELECT '2008', 10002, 'Senior IRS Lien' UNION ALL
SELECT '2008', 10003, 'IRS Lien' UNION ALL
SELECT '2008', 10003, 'Senior IRS Lien' UNION ALL
SELECT '2008', 10003, 'Jr. Lien' UNION ALL
SELECT '2008', 10004, 'HOA Lien'

CREATE TABLE #TGSITEMS1 (
  ID INT IDENTITY,
  ITEM VARCHAR(MAX)
)

DECLARE @VAR INT
DECLARE @CUR CURSOR
DECLARE @VAR1 INT
DECLARE @CMD VARCHAR(1000)
DECLARE @CMD1 VARCHAR(1000)

SET @CUR = CURSOR FOR
SELECT DISTINCT SEQNUMBER
FROM #TGSITEMS

OPEN @CUR
FETCH NEXT FROM @CUR INTO @VAR

WHILE @@FETCH_STATUS = 0
BEGIN
  TRUNCATE TABLE #TGSITEMS1

  INSERT INTO #TGSITEMS1 (ITEM)
  SELECT ITEM FROM #TGSITEMS WHERE SEQNUMBER = @VAR ORDER BY ITEM

  SET @VAR1 = 1
  SET @CMD1 = ''

  WHILE @VAR1 <= (SELECT COUNT(*) FROM #TGSITEMS1)
  BEGIN
    SELECT @CMD = ITEM FROM #TGSITEMS1 WHERE ID = @VAR1

    IF @VAR1 < (SELECT COUNT(*) FROM #TGSITEMS1) - 1
      SET @CMD1 = @CMD1 + @CMD + ','
    ELSE IF @VAR1 = (SELECT COUNT(*) FROM #TGSITEMS1) - 1
      SET @CMD1 = @CMD1 + @CMD
    ELSE IF (SELECT COUNT(*) FROM #TGSITEMS1) = 1
      SET @CMD1 = @CMD
    ELSE
      SET @CMD1 = @CMD1 + ' and ' + @CMD

    SET @VAR1 = @VAR1 + 1
  END

  INSERT INTO #TGSITEMS
  SELECT DISTINCT ORDERYEAR, SEQNUMBER, @CMD1 FROM #TGSITEMS WHERE SEQNUMBER = @VAR

  FETCH NEXT FROM @CUR INTO @VAR
END

CLOSE @CUR
DEALLOCATE @CUR

SELECT DATENAME(YY, ORDERYEAR) AS ORDERDATE, SEQNUMBER, ITEM FROM #TGSITEMS

TRUNCATE TABLE #TGSITEMS

It’s important to note that this approach can be performance-consuming, so it’s recommended to consider alternative solutions if performance is a concern.

We appreciate the contribution of Imran Mohammed, who provided a solution to Anthony’s challenge. Anthony expressed his gratitude for the help and mentioned his desire to move away from MS Access and embrace SQL Server.

Thank you both for utilizing this platform to solve technical problems and contribute to the SQL Server community.

Stay tuned for more SQL Server concepts and ideas in our future blog posts!