SQL - Move multiple rows worth of data to one row

Question

What I am trying to do is take multiple rows of data from a column and insert it into a single cell. Here's what I have below:

+++HouseNumber+++++++CustomerType+++
+      1         +    Residential  +
+      2         +    Commercial   +
+      2         +    Residential  +
+      3         +    Residential  +
++++++++++++++++++++++++++++++++++++

And I need to get this to something that looks like this:

+++HouseNumber+++++++CustomerType+++++++++++++++
+      1         +    Residential              +
+      2         +    Commercial Residential   +
+      3         +    Residential              +
++++++++++++++++++++++++++++++++++++++++++++++++

I realize that this is against the normalization thing; however, I simply need this data displayed this way so that I can view it more easily later on, the particular cell will never again be referenced for any individual item within it.

I tried to do this by creating two tables, one with a tempCustomerType, and one with a a customerType field orignally NULL and then update using the following:

UPDATE CustomerIdentifier
SET CustomerIdentifier.CustomerType = TempTable2.CustomerTypeTemp + CustomerIdentifier.CustomerType
FROM CustomerIdentifier
INNER JOIN TempTable2
ON CustomerIdentifier.SUB_ACCT_NO_OCI = TempTable2.SUB_ACCT_NO_OCI

However, after that each field was still null. So, any chance anyone here can help me? Thanks!

Also, if there is a way to do this without creating a second table, that would be great as well.

Answer 1

NULL + 1 in T/SQL always will return null;

The solutions for you problem are described here

Answer 2

We implemented our own CLR aggregate function as described here, you can then write:

DECLARE @test TABLE (
    HouseNumber INT,
    CustomerType VARCHAR(16)
)

INSERT INTO @test
      SELECT 1, 'Residential'
UNION SELECT 2, 'Commercial'
UNION SELECT 2, 'Residential'
UNION SELECT 3, 'Residential'

SELECT HouseNumber, dbo.Concatenate(CustomerType)
FROM @test
GROUP BY HouseNumber

Answer 3

Below a simpler solution to the problem. Unfortunately untested on my machine (sql server install borked), I will test tomorrow and edit the answer if necessary. This will work with SQL 2005 and above and doesn't require any UDFs or CLR. It is also pretty fast too.

/* Test Table & Data */
DECLARE @TestTable TABLE
(
HouseNumber int,
CustomerType varchar(12)
)
;
INSERT @TestTable
SELECT 1, 'Residential' UNION ALL
SELECT 2, 'Commercial'  UNION ALL
SELECT 2, 'Residential' UNION ALL
SELECT 3, 'Residential' UNION ALL
;
/* CTE to construct the concatenated data. */
WITH ConcatData (HouseNumber,CustomerType) as
(
SELECT HouseNumber,STUFF((SELECT ', ' + CustomerType
FROM @TestTable TT2
FOR XML PATH ('')
WHERE TT2.HouseNumber = TT1.HouseNumber),1,2,'')
FROM TestTable TT1
GROUP BY TT1.HouseNumber
)
/* Update the test table using the concatenated data from the CTE - joining on HouseNumber */
UPDATE trg
SET CustomerType = src.CustomerType
FROM @TestTable trg
INNER JOIN ConcatData src on src.HouseNumber = trg.HouseNumber