Based on the stated requirements... I think this is what you're looking for...

IF OBJECT_ID('tempdb..#temp') IS NOT NULL

DROP TABLE #temp;

CREATE TABLE #temp([term] int,

...

Steve - Very nice solution! 🙂

sgmunson (7/29/2015)

---

EDIT: There's a need to replace the XML representation of the greater than symbol with the actual greater than symbol, but this forum doesn't...

I tried loading up your new test data... It's throwing errors. Please check your insert statements.

Also, as a matter of convenience and readability... Please post code inside a sql code...

The computer is off for the night. I'll retest tomorrow with your updated test data.

petervdkerk (7/28/2015)

---

Thanks again. I really need just SQL and not stored procedures as I want to use the SQL in my Solr data-config.xml which does not support stored procedures. My...

Here is another option that uses "Nested Sets". The stored proc (dbo.CreateNestedSets) is based off of the code provided by Jeff Moden (Hierarchies on Steroids #1: Convert an Adjacency List...

petervdkerk (7/28/2015)

---

@jason: Thanks. But your query returns 552 rows for the few products I have and for the resulting rows that have a value for artikelid the artikelgroups are not...

petervdkerk (7/28/2015)

---

@Lynn: this looks great already! Thanks!

I now see that this groups all article groups, independent of the productid...how would I alter this query to return only the concatenated...

The actual text of stored proc can be found in sys.sql_modules.

When doing a concatenation... NULL + anything = NULL.

Wrapping NULL values in a COALESCE or ISNULL function can convert null values into empty strings (blanks)

COALESCE(t.ColumnName, '') or ISNULL(t.ColumnName, '')

Sean Lange (7/27/2015)

---

Jason A. Long (7/27/2015)

---

This should give you what you're looking for...

SELECT ID, [Date],0 AS T1, I1 FROM K1

UNION ALL

SELECT ID, [Date], T1, 0 AS I1...

This should give you what you're looking for...

SELECT ID, [Date],0 AS T1, I1 FROM K1

UNION ALL

SELECT ID, [Date], T1, 0 AS I1 FROM K2

ORDER BY ID, [Date]

Based on what you have and the output that you have shown, the following should give you what you're looking for...

SELECT

m1.ParentID AS Account_ID,

STUFF((

SELECT ', ' + CAST(m2.ChildID AS VARCHAR(8))

FROM...

There's also no need to write this as a CROSS JOIN... It's actually what Jeff would refer to as a "triangular join".

SELECT

CityName

INTO #City

FROM (VALUES ('Jax'),('Orlando'),('Miami'),('Tally'),('Appalach'),('Lake City'),('Panama City')) City (CityName)

SELECT

c1.CityName...

Here's yet another version...

IF OBJECT_ID('tempdb..#temp') IS NOT NULL

DROP TABLE #temp;

SELECT 'S3925' AS partnum, 'MDSHT 3000 x 1500 x 6mm (7.5mmO/A HEIGHT) MS' AS partdescription INTO #temp;

SELECT

t.partnum,

MAX(CASE WHEN sc.ItemNumber...