SQL Server query to create bogus rows

I don't believe SQL can help me here, requiring me to manipulate the data in code. I hate to do that, so here's hoping I'm wrong...

I have a query that returns 1 to many rows. It includes EmpKey, which is always populated. Example...

1,[other fields]
1,...
1,...
1,...
2,...
2,...
2,...
2,...
2,...
2,...
2,...

For each group of EmpKey, I need the following...

a) If # of rows for an EmpKey is < 6, I need "dummy rows" of that EmpKey for a total of 6 rows.
b) If # of rows for an EmpKey is > 6, I need "dummy rows" of that EmpKey for a total of 12 rows.

So for the example output above, I instead need the output to be...

1,[other fields]
1,...
1,...
1,...
1,[all other fields are null] (query created)
1,[all other fields are null] (query created)
2,...
2,...
2,...
2,...
2,...
2,...
2,...
2,[all other fields are null] (query created)
2,[all other fields are null] (query created)
2,[all other fields are null] (query created)
2,[all other fields are null] (query created)
2,[all other fields are null] (query created)

Any idea if this is doable in SQL Server?
jjsatherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
You can do it using a temp table:

so if your original query is:

select * from table (just saying the basic querY)

change it to :

select *
into #temp  -- add this before the "from" in your query
from table

we can now work with the #temp table.


next part would be to get the count(*) for each id:
--get number of rows needed, the mod 6 will tell you how many are left
select count(*) % 6 RowsNeeded, id
into #needed
from #temp
group by id

Open in new window


and then finally process that table:

declare @id int
declare @rowCount int

-- for each id that needs rows
while (select count(*) from #needed) > 0
begin
  --get the top id
   select top 1 @id = id, @rowCount = RowsNeeded from #needed
     -- loop inserting that number of rows.
    while @rowCount > 0 
     begin
           insert into #temp (id) @id
          set @rowCount = @rowCount -1
     end
     --clear the #needed of the id just processed
     delete from #needed where id = @id
end

-- final query
select * from #temp


-- cleanup
drop table #temp
drop table #needed

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jjsatherAuthor Commented:
Took me a while to see it, but it appears the code I would write in my program, you instead wrote in SQL. I should have said I'm making a view, not a proc, so I can't use temp tables.

I could use CTEs (though I'm not real handy with them) so maybe this can somehow still work? If one could CTE a table that with the needed empty rows, then union it with the main query? I'm not sure.
Kyle AbrahamsSenior .Net DeveloperCommented:
let's see if we can do this:
with cte as (
select *, row_number() over (partition by id) as RN
from table
);
with cte2 as (
select 1 RN
union all select 2 
union all select 3 
union all select 4 
union all select 5
union all select 6
)
with cte3 as (
select 1 RN
union all select 2 
union all select 3 
union all select 4 
union all select 5
union all select 6
--repeat to 12
)


select c1.*, isnull(c1.RN, c2.RN) RN
from cte2 c2 
left join cte  c1 on c2.RN = c1.RN
group by  -- add all c1 columns
having count(c1.*) < 6

union all

select c1.*, isnull(c1.RN, c2.RN) RN
from cte c3
left join cte c1  on c2.RN = c1.RN
group by --add all c1 columns
having count(c1.*) <  12 and count(c1.*) > 6

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jjsatherAuthor Commented:
It seems close, but something is off, and I'm not smart enough to take it all the way. I got it this far by filling in the blanks and correcting syntax and names, but I'm still getting the same number of rows.

Hopefully this will help if you point the base query at a table of yours to get the same general affect...



alter view [dbo].[vprTest]

as

with cteBaseQuery as (
select EmployeeKey,
       EmployeeName,
       BirthDate
from tprDependents),


cte as (
select *, row_number() over (partition by EmployeeKey order by EmployeeKey) as RN1
from cteBaseQuery
),
cte2 as (
select 1 RN2
union all select 2 
union all select 3 
union all select 4 
union all select 5
union all select 6
),
cte3 as (
select 1 RN3
union all select 2 
union all select 3 
union all select 4 
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
union all select 10
union all select 11
union all select 12
)



select c1.*, isnull(c1.RN1, c2.RN2) RN
from cte2 c2 
left join cte  c1 on c2.RN2 = c1.RN1
group by EmployeeKey, EmployeeName, BirthDate, c1.RN1, c2.RN2
having count(*) < 6

union all

select c1.*, isnull(c1.RN1, c3.RN3) RN
from cte3 c3
left join cte c1  on c3.RN3 = c1.RN1
group by EmployeeKey, EmployeeName, BirthDate, c1.RN1, c3.RN3
having count(*) < 12 and count(*) > 6

GO

Open in new window

PortletPaulEE Topic AdvisorCommented:
This query
SELECT DISTINCT
      t.empcode
    , t.frequency
    , r.rn
FROM (
      SELECT
            empcode
          , COUNT(*) OVER (PARTITION BY empcode) AS frequency
      FROM test
      ) AS t
CROSS JOIN  (SELECT 1 AS RN
              UNION ALL SELECT 2
              UNION ALL SELECT 3
              UNION ALL SELECT 4
              UNION ALL SELECT 5
              UNION ALL SELECT 6
              UNION ALL SELECT 7
              UNION ALL SELECT 8
              UNION ALL SELECT 9
              UNION ALL SELECT 10
              UNION ALL SELECT 11
              UNION ALL SELECT 12
              )  AS r
WHERE (t.frequency <= 6 and r.rn <= 6)
OR (t.frequency > 6 and r.rn <= 12)

Open in new window

using this test data
CREATE TABLE test
    ([empcode] varchar(20))
;
    
INSERT INTO test
    ([empcode])
VALUES
    ('4off'),
    ('4off'),
    ('4off'),
    ('4off'),
    
    ('6off'),
    ('6off'),
    ('6off'),
    ('6off'),
    ('6off'),
    ('6off'),
    
    ('9off'),
    ('9off'),
    ('9off'),
    ('9off'),
    ('9off'),
    ('9off'),
    ('9off'),
    ('9off'),
    ('9off'),
    
    ('12off'),
    ('12off'),
    ('12off'),
    ('12off'),
    ('12off'),
    ('12off'),
    ('12off'),
    ('12off'),
    ('12off'),
    ('12off'),
    ('12off'),
    ('12off'),
    
    ('15off'),
    ('15off'),
    ('15off'),
    ('15off'),
    ('15off'),
    ('15off'),
    ('15off'),
    ('15off'),
    ('15off'),
    ('15off'),
    ('15off'),
    ('15off'),
    ('15off'),
    ('15off'),
    ('15off')
    ;

Open in new window

produced this result:
| empcode | frequency | rn |
|---------|-----------|----|
|   12off |        12 |  1 |
|   12off |        12 |  2 |
|   12off |        12 |  3 |
|   12off |        12 |  4 |
|   12off |        12 |  5 |
|   12off |        12 |  6 |
|   12off |        12 |  7 |
|   12off |        12 |  8 |
|   12off |        12 |  9 |
|   12off |        12 | 10 |
|   12off |        12 | 11 |
|   12off |        12 | 12 |
|   15off |        15 |  1 |
|   15off |        15 |  2 |
|   15off |        15 |  3 |
|   15off |        15 |  4 |
|   15off |        15 |  5 |
|   15off |        15 |  6 |
|   15off |        15 |  7 |
|   15off |        15 |  8 |
|   15off |        15 |  9 |
|   15off |        15 | 10 |
|   15off |        15 | 11 |
|   15off |        15 | 12 |
|    4off |         4 |  1 |
|    4off |         4 |  2 |
|    4off |         4 |  3 |
|    4off |         4 |  4 |
|    4off |         4 |  5 |
|    4off |         4 |  6 |
|    6off |         6 |  1 |
|    6off |         6 |  2 |
|    6off |         6 |  3 |
|    6off |         6 |  4 |
|    6off |         6 |  5 |
|    6off |         6 |  6 |
|    9off |         9 |  1 |
|    9off |         9 |  2 |
|    9off |         9 |  3 |
|    9off |         9 |  4 |
|    9off |         9 |  5 |
|    9off |         9 |  6 |
|    9off |         9 |  7 |
|    9off |         9 |  8 |
|    9off |         9 |  9 |
|    9off |         9 | 10 |
|    9off |         9 | 11 |
|    9off |         9 | 12 |

Open in new window

jjsatherAuthor Commented:
This is very close. Works fine for the data you provided, but when I add even 1 more field, it doesn't. (And I have several fields I need to include.) See how I modified the query and data below...


SELECT DISTINCT
      t.empcode
    , t.miscdata  
    , t.frequency
    , r.rn
FROM (
      SELECT
            empcode
          ,  miscdata
          , COUNT(*) OVER (PARTITION BY empcode) AS frequency
      FROM test
      ) AS t
CROSS JOIN  (SELECT 1 AS RN
              UNION ALL SELECT 2
              UNION ALL SELECT 3
              UNION ALL SELECT 4
              UNION ALL SELECT 5
              UNION ALL SELECT 6
              UNION ALL SELECT 7
              UNION ALL SELECT 8
              UNION ALL SELECT 9
              UNION ALL SELECT 10
              UNION ALL SELECT 11
              UNION ALL SELECT 12
              )  AS r
WHERE (t.frequency <= 6 and r.rn <= 6)
OR (t.frequency > 6 and r.rn <= 12)

Open in new window



CREATE TABLE test
    ([empcode] varchar(20),
    [miscdata] varchar(20))
;
    
INSERT INTO test
    ([empcode],[miscdata])
VALUES
    ('4off','1'),
    ('4off','2'),
    ('4off','3'),
    ('4off','4'),
    
    ('6off','5'),
    ('6off','6'),
    ('6off','7'),
    ('6off','8'),
    ('6off','9'),
    ('6off','10'),
    
    ('9off','11'),
    ('9off','12'),
    ('9off','13'),
    ('9off','14'),
    ('9off','15'),
    ('9off','16'),
    ('9off','17'),
    ('9off','18'),
    ('9off','19'),
    
    ('12off','20'),
    ('12off','21'),
    ('12off','22'),
    ('12off','23'),
    ('12off','24'),
    ('12off','25'),
    ('12off','26'),
    ('12off','27'),
    ('12off','28'),
    ('12off','29'),
    ('12off','30'),
    ('12off','31'),
    
    ('15off','32'),
    ('15off','33'),
    ('15off','34'),
    ('15off','35'),
    ('15off','36'),
    ('15off','37'),
    ('15off','38'),
    ('15off','39'),
    ('15off','40'),
    ('15off','41'),
    ('15off','42'),
    ('15off','43'),
    ('15off','44'),
    ('15off','45'),
    ('15off','46')
    ;

Open in new window



With this data (and adding that column to the query), rather than return 48 rows, it returns 492 rows. I put my "main query" in that subquery, but maybe that's not where it should go. (FYI, my base query is a bit complicated and includes a union.)
PortletPaulEE Topic AdvisorCommented:
There is a problem with my approach, I am reliant on "select distinct" as as this considers the entire rows (all values of the whole row) you can see the effect immediately.

Can we see your whole query please? (your original query that is)

Sometimes trying to add something on top is the wrong way to do it, it may be more effective to do the "bogus rows" earlier in the query.

Also, not having any valid data to play with makes it way harder to propose solutions.
jjsatherAuthor Commented:
Whatever others find, I just assumed I'd wrap my original query in a cte, which is why my original post said it had other misc data.

However, I think I'll tweak the data in code rather than rely on SQL for this. It seems convoluted and hard to debug, unlike the code side (at least for me). I'll give credit for the good responses, but here's the original query if some still want to play with the idea...

select distinct
       E.EmployeeKey,
       null as DependentKey,
       E.NameFirstLast,
       E.BirthDate
from dbo.tprCoverDates CD
join vprEmployees E on E.EmployeeKey = CD.EmployeeKey
union all
select distinct
       D.EmployeeKey,
       D.DependentKey,
       D.NameFirstLast,
       D.BirthDate
from dbo.tprCoverDates CD
join dbo.vhrDependents D on D.DependentKey = CD.DependentKey
and CD.CoverageDateKey is not null
PortletPaulEE Topic AdvisorCommented:
Thanks. I agree, do it in code.
Vitor MontalvãoMSSQL Senior EngineerCommented:
jjsather, do you still need help with this question?
jjsatherAuthor Commented:
No, I'll just accept these as partial answers, but just write it in code. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.