Adding columns in a query executed from a stored procedure

Question

I have a query in a stored procedure like below

select x,y from table

and the results will look like below

x y
1 a
1 b
2 a
2 b
3 a
3 b

i need to add a blank column or zeros when the value of x changes like below

x y
1 a
1 b
0 0
2 a
2 b
0 0
3 a
3 b

Can this be done by sql or since i'm using the data for birt reports can this be done with birt?

Answer 1

You need UNION ALL to add the extra rows, you also need to ORDER them, the DENSE_RANK is to get rid of the extra row.

here is how it could be done:

DECLARE @t table(x int, y char(1))
INSERT @t values
(1,'a'),(1,'b'),(2,'a'),
(2,'b'),(3,'a'),(3,'b')

;WITH CTE AS
(
  SELECT
    2 rn, x,y, x sort from @t
  UNION ALL
  SELECT
    distinct dense_rank() over (order by x desc) rn,  0, '0', x+.1 sort
  FROM @t
)
SELECT x,y
FROM CTE
WHERE rn > 1
ORDER BY sort, x

Result:

x   y
1   a
1   b
0   0
2   a
2   b
0   0
3   a
3   b

Answer 2

declare @t table (X varchar(1),Y varchar(1))
insert into @t(X,y) values (1,'A'),
(1,'B'),
(2,'A'),
(2,'B'),
(3,'A'),
(3,'B')


;with CTE As(
select X,Y,ROW_NUMBER()OVER(PARTITION BY X,Y ORDER BY X)RN
from @t
CROSS APPLY
(
    values
        ('',NULL),
        ('',NULL)
) C(R, N)),
CTE2 AS(

Select CASE WHEN RN > 1 THEN 0 ELSE X END X ,
      CASE WHEN RN > 1 THEN CAST(0 AS VARCHAR) ELSE Y END ID 
      ,ROW_NUMBER()OVER(PARTITION BY X ORDER BY (SELECT NULL)) R
       FROM CTE
)
select X,ID from cte2 where R <> 2

Answer 3

This is working example:

DECLARE @DataSource TABLE
(
    [x] TINYINT
   ,[y] CHAR(1)
);

INSERT INTO @DataSource ([x], [y])
VALUES (1, 'a')
      ,(1, 'b')
      ,(2, 'a')
      ,(2, 'b')
      ,(3, 'a')
      ,(3, 'b');

WITH DataSource AS
(
    SELECT *
    FROM @DataSource
    UNION ALL
    -- the NULL will be always displayed on the first position
    SELECT DISTINCT [x]
                   ,NULL
    FROM @DataSource
)
SELECT IIF([Rank] = 1, 0, [x])
      ,IIF([Rank] = 1, 0, [x])
FROM
(
    SELECT ROW_NUMBER() OVER(PARTITION BY [x] ORDER BY [y]) AS [Rank]
          ,[x]
          ,[y]
    FROM DataSource
) DS
ORDER BY [x]
        ,[Rank]

Few important notes:

• the NULL values for each x will be with rank 1 always
• the final result set is sorted by x and rank both