Join 2 tables and create date columns

Queennie L
Queennie L used Ask the Experts™
on
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.
Join-2-tables-A-and-B.xlsx
Comment
Watch Question

Do more with

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

Commented:
Hi,

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.



Regards,
    Tomas Helgi

Author

Commented:
@TomasHelgi:

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

Thank you for your help.
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
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)
		 grp
	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
		,codeno
		,a.grp
		,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
pivot(
	max(mydate)
	for d_rnk in ([1],[2],[3],[4])
) p
order by stno, grp
;

Open in new window


My test case is here:
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=a48432979a2a5093060c6ab9c6aac72e
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.

Author

Commented:
@slightwv:

I will test it and let you know.

Thank you again for your help.

Author

Commented:
@slightwv:

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

Thank you.

Author

Commented:
@slightwv:

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

Author

Commented:
@slightwv:

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

Commented:
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