T-SQL: How to return parent and child records in a certain format?

With T-SQL, is it possible to write a query that returns results that look like this?

Parent A        
   ChildOfParentA_1 ChildField1 ChildField2
   ChildOfParentA_2 ChildField1 ChildField2
   ChildOfParentA_3 ChildField1 ChildField2
Parent B        
   ChildOfParentB_1 ChildField1 ChildField2
   ChildOfParentB_2 ChildField1 ChildField2
   ChildOfParentB_3 ChildField1 ChildField2

In these results, there 开发者_C百科is a row that contains only one field for each parent record, with extra fields being set to NULL. Then, beneath parent record, there is a row for child records, which has three fields.

I know how to accomplish this using temporary tables and cursors, but I'd really like to have a single query that would return this information, in this format, if it's even possible.

---

I'm assuming your parent and child records are in different tables, and can be joined by a ParentID that resides on both tables. The following query selects records from both tables, formats them such that they have the same number of columns, unions them together, and outputs them with a little formatting.

SELECT ParentName, ChildName, ChildField1, ChildField2
FROM
  (SELECT pt.ParentID, 0 ChildID, pt.ParentName, '' ChildName, '' ChildField1, '' ChildField2
   FROM ParentTable pt
   UNION
  SELECT ct.ParentID, ct.ChildID, '   ' ParentName, ct.ChildName, ct.ChildField1, ct.ChildField2
  FROM ChildTable ct)

ORDER BY ParentID, ChildID

Standard disclaimer: SQL is not really designed for formatting output, so if you can shift the responsibility for the tabbing/indentation to the presentation layer, that's usually better. But if you must do it in SQL (and sometimes we must!) this approach should do the trick. :)

Good luck!

---

There is no such thing as a row that contains only one field in a multi field result set (even using temporary tables and cursors).

You can set the other fields to NULL for a parent record.

SELECT Name, FieldA, FieldB
FROM (
    SELECT 1 Type, p.ID ID, NULL SubID, p.Name Name, NULL FieldA, NULL FieldB
    FROM hierarchyTable p
    WHERE p.ParentID IS NULL
    UNION ALL SELECT 2, c.ParentID, c.ID, c.Name, FieldA, FieldB
    FROM hierarchyTable c
    WHERE c.ParentID IS NOT NULL
) rs
ORDER BY ID, Type, SubID

Alternatively, you can use a single query:

SELECT Name,
    CASE WHEN ParentID IS NULL
        THEN NULL
        ELSE FieldA
    END AS FieldA,
    CASE WHEN ParentID IS NULL
        THEN NULL
        ELSE FieldB
    END AS FieldB
FROM hierarchyTable
ORDER BY COALESCE(ParentID, ID),
    CASE WHEN ParentID IS NULL THEN 0 ELSE 1 END,
    ID

---

Maybe this will do the trick

Test data:

declare @t table(var1 varchar(20), var2 varchar(20), var3 varchar(20))

insert @t values('ParentA', null, null)
insert @t values('ParentA', 'a', 'b')
insert @t values('ParentA', 'c', 'd')
insert @t values('ParentA', 'e', 'f')
insert @t values('ParentB', null, null)
insert @t values('ParentB', 'g', 'h')
insert @t values('ParentB', 'i', 'j')
insert @t values('ParentB', 'k', 'l')

Query:

SELECT a FROM (
SELECT var1 a, 1 s , var1
FROM @t 
WHERE var2 is NULL
UNION ALL
SELECT 'ChildOf' + var1 + '_' + CAST(RN AS VARCHAR) + ' ' + var2 + ' ' + var3, 2, var1
FROM 
(SELECT *, 
RN = ROW_NUMBER() OVER (PARTITION BY VAR1 ORDER BY (SELECT 1))
FROM @t where var2 is not null
) t
) b ORDER BY var1, s