Dividing one SQL column into subgroups with headings

Question

I have two tables

STATUS

SNO | STATUS | DEPARTMENT_ID

1   In progress      1
2    Assigned        2
3     Quoted         2
4   Development      3

DEPARTMENTS

SNO | DEPARTMENT |

1     DESIGNING
2     MARKETING
3     PRODUCTION

Now I want a result like this using SQL stored procedure

Some Custom Column Name | DEPARTMENT_ID

DESIGNING                      -
In Progress                    1
MARKETING                      -
Assigned                       2
Quoted                         2
PRODUCTION                     -
Development                    3

The custom column will be used to populate a Telerik RadComboBox with DESIGNING, MARKETING and PRODUCTION acting as separators between statuses.

score 0 · Answer 1 · answered Feb 19 '15 at 12:45

0

Select Department, -1 from Department_Table
Union
Select StatusName, Department_ID from Status_Table

Please elaborate your question so that we can provide better answer. Currently it seems you just want to return the joined data of both tables.

answered Feb 19 '15 at 12:45

Sorrel Vesper

414
4
18

score 0 · Answer 2 · answered Feb 19 '15 at 12:47

0

Often, this type of operation is more easily done at the application level. You can do it in SQL, using union all and order by, however:

select status as CustomColumnName, department
from ((select s.status, d.department, 1 as ordering
       from status s join
            departments d
            on s.department_id = d.sno
      ) union all
      (select d.department, NULL, 0 as ordering
       from departments d
      )
     ) dd
order by CustomColumnName, ordering;

Note: this treats the - as NULL.

answered Feb 19 '15 at 12:47

Gordon Linoff

1,242,037
58
646
786

Ah. Sorry. I missed the ordering part. – Sorrel Vesper Feb 19 '15 at 12:50
the ordering part is needed? – Kalaiarasan Manimaran Feb 19 '15 at 12:53
1

@KalaiarasanManimaran . . . Yes. The OP seems to have a specific ordering in mind. – Gordon Linoff Feb 19 '15 at 13:03

score 0 · Answer 3 · answered Feb 19 '15 at 13:05

Try this.Is it ok with other sample data ?

DECLARE @STATUS TABLE (
    SNO INT
    ,[STATUS] VARCHAR(50)
    ,DEPARTMENT_ID INT
    )

INSERT INTO @STATUS
VALUES (1,'In progress' ,1)
    ,(2,'Assigned',2)
    ,(3,'Quoted',2)
    ,(4,'Development',3)

DECLARE @DEPARTMENT TABLE (SNO INT,DEPARTMENT VARCHAR(50))

INSERT INTO @DEPARTMENT
VALUES (    1,'DESIGNING'),(2,'MARKETING')
    ,(3,'PRODUCTION')
    --select * from @STATUS
    --select * from @DEPARTMENT
    ;

WITH CTE
AS (
    SELECT DEPARTMENT [CustomeColumn]
        ,'-' DEPARTMENT_ID
        ,sno
    FROM @DEPARTMENT

    UNION ALL

    SELECT [STATUS]
        ,cast(DEPARTMENT_ID AS VARCHAR(10))
        ,(
            SELECT sno
            FROM @DEPARTMENT
            WHERE sno = a.DEPARTMENT_ID
            )
    FROM @STATUS A
    )
SELECT *
FROM CTE
ORDER BY sno

Dividing one SQL column into subgroups with headings

3 Answers3