CODEWITHSUNDEEP
t-sqlgroup-by
Munawar
Munawar

Reputation: 2587

Make a column values header for rest of columns using TSQL

I have following table

ID | Group      | Type         | Product
1    Dairy         Milk           Fresh Milk
2    Dairy         Butter         Butter Cream
3    Beverage      Coke           Coca cola
4    Beverage      Diet           Dew
5    Beverage      Juice          Fresh Juice

I need following output/query result:

ID | Group      | Type         | Product
1    Dairy             
1                 Milk           Fresh Milk
2                 Butter         Butter Cream
2    Beverage      
1                Coke            Coca cola
2                Diet            Dew
3                Juice           Fresh Juice

For above sample a hard coded script can do the job but I look for a dynamic script for any number of groups. I do not have any idea how it can be done so, I do not have a sample query yet. I need ideas, examples that at least give me an idea. PIVOT looks a close option but does not looks to be fully fit for this case.

Upvotes: 2

Views: 822

Answers (3)

Sebastian Meine
Sebastian Meine

Reputation: 11813

And an approach with ROW_NUMBER:

IF OBJECT_ID('dbo.grouprows') IS NOT NULL DROP TABLE dbo.grouprows;

CREATE TABLE dbo.grouprows(
ID INT,
 Grp NVARCHAR(MAX),
 Type NVARCHAR(MAX),
 Product NVARCHAR(MAX)
 );
 INSERT INTO dbo.grouprows VALUES
(1,'Dairy','Milk','Fresh Milk'),
(2,'Dairy','Butter','Butter Cream'),
(3,'Beverage','Coke','Coca cola'),
(4,'Beverage','Diet','Dew'),
(5,'Beverage','Juice','Fresh Juice');

SELECT 
CASE WHEN gg = 0 THEN dr1 END GrpId,
CASE WHEN gg = 1 THEN rn1 END TypeId,
ISNULL(Grp,'')Grp,
CASE WHEN gg = 1 THEN Type ELSE '' END Type,
CASE WHEN gg = 1 THEN Product ELSE '' END Product
FROM(
SELECT *,
DENSE_RANK()OVER(ORDER BY Grp DESC) dr1
FROM( 
SELECT *,
ROW_NUMBER()OVER(PARTITION BY Grp ORDER BY type,gg) rn1,
ROW_NUMBER()OVER(ORDER BY type,gg) rn0
FROM(
SELECT  Grp,Type,Product, GROUPING(Grp) gg, GROUPING(type) tg FROM dbo.grouprows
GROUP BY Product, Type, Grp
WITH ROLLUP
)X1
WHERE tg = 0
)X2
WHERE gg=1 OR rn1 = 1
)X3
ORDER BY rn0

Upvotes: 0

Thomas
Thomas

Reputation: 64655

A slightly simplified approach:

With Groups As
    (
    Select Distinct Min(Id) As Id, [Group], '' As [Type], '' As Product
    From dbo.Source
    Group By [Group]
    )
Select Coalesce(Cast(Z.Id As varchar(10)),'') As Id
  , Coalesce(Z.[Group],'') As [Group]
  , Z.[Type], Z.Product
From    (
        Select Id As Sort, Id, [Group], [Type], Product
        From Groups
        Union All
        Select G.Id, Null, Null, S.[Type], S.Product
        From dbo.Source As S
            Join Groups As G
                On G.[Group] = S.[Group]
        ) As Z
Order By Sort

It should be noted that the use of Coalesce is purely for aesthetic reasons. You could simply return null in these cases.

SQL Fiddle

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460208

Here's a possible way. It basically unions the "Group-Headers" and the "Group-Items". The difficulty was to order them correctly.

WITH CTE AS
(
  SELECT ID,[Group],Type,Product,
    ROW_NUMBER() OVER (PARTITION BY [Group] Order By ID)AS RN
  FROM Drink
)
SELECT ID,[Group],Type,Product
FROM(
   SELECT RN AS ID,[Group],[Id]AS OriginalId,'' As Type,'' As Product, 0 AS RN, 'Group' As RowType
   FROM CTE WHERE RN = 1
   UNION ALL
   SELECT RN AS ID,'' AS [Group],[Id]AS OriginalId,Type,Product, RN, 'Item' As RowType
   FROM CTE
)X
ORDER BY  OriginalId ASC
, CASE WHEN RowType='Group' THEN 0 ELSE 1 END ASC
, RN ASC

Here's a demo-fiddle: http://sqlfiddle.com/#!6/ed6ca/2/0

Upvotes: 2

Related Questions