Join 2 tables and create date columns

Queennie L
Queennie L used Ask the Experts™
Hello Experts,

I have 2 SQL tables A and B.

A table has all the dates in "Date" column.
B table has different values in "CodeNo" column.

What I want to output is join "Date" column in A table to B table.
Create "Date_1" column for the first "CodeNo" if then create another "Date_2" column for the rest of the values in "Date" column in A table.

Is this possible?
Thank you for all your help.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Tomas Helgi JohannssonDatabase Administrator / Software Engineer


The example data you provide tells me that the table B lacks some additional columns and data so
that you can do some simple SQL joins. I personally would have the date column in table B.
That would give you ability to use GROUP BY on STNo,InSNo and CodeNo columns and group the relevant dates together for further data manipulation.

    Tomas Helgi



My problem is I cannot make it to create a new column.

Thank you for your help.
Most Valuable Expert 2012
Distinguished Expert 2018
You will need to know how many possible wrapped dates you will EVER need.  This is due to the PIVOT needing to know how many columns at parse time.

See if this works for you:
with a_rows as (
	select stno, insno, mydate,
			(row_number() over(partition by stno, insno order by mydate)-1)
			% (select count(*) from tableb b where a.stno=b.stno and a.insno=b.insno)
	from tablea a
b_rows as (
	select stno, insno, codeno,
		row_number() over(partition by stno, insno order by codeno)-1 grp
	from tableb
summary as (
	select a.stno, a.insno, mydate
		,dense_rank() over(partition by a.stno, a.grp order by mydate) d_rnk
	from a_rows a
		join b_rows b on a.stno=b.stno and a.insno=b.insno and a.grp=b.grp
select stno, insno, codeno, [1], [2], [3], [4]
from summary
	for d_rnk in ([1],[2],[3],[4])
) p
order by stno, grp

Open in new window

My test case is here:
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.



I will test it and let you know.

Thank you again for your help.



When I run this code, there is an error mydate and codeno invalid column names.

Thank you.



It is my fault. I invert 2 tables in the wrong place.



OMG! It is working.

Thank you for sharing your expertise and intelligence.

I really appreciate it.

Thank you also those who give their input.
Most Valuable Expert 2012
Distinguished Expert 2018

Happy to help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial