how to get all values from one table repeated to each group on a column in another table

Question

how to get all values from one table repeated to each group on a column in another table

Naidu PK

SSCrazy

Points: 2481
More actions
January 26, 2018 at 1:54 pm

#326621

Updated Query tables:
I have a table called DisplayFields and another one called tax.
Displayfields table has all unique rows (master table). I want all the rows from DisplayFields table to each tax year whether it matches or not with MFN columns. In essence, the 3 rows in DisplayFields is repeated for each tax year.
I tried cross join but struggling to filter out the unwanted data.
DECLARE @DisplayFields as TABLE     (                  [LineNumber] nvarchar(250),         MFN nvarchar(500)     )     Insert @DisplayFields ( [LineNumber],MFN)     Values ( '1','abcd' ), ('1a', 'bcde'), ('2a', 'cdef')
   declare @tax as table     (         MFN nvarchar(500),         TaxYear Int,         Value Int     )     insert @tax (MFN, TaxYear, Value)     Values ('abcd',    2016, 100), ('bcde'    ,2016,200), ('abcd'    ,2017,300)
Desired output:
LineNumber MFN TaxYear Value
   1 abcd   2016      100
    1a    bcde 2016     200
    2a    cdef null       Null
    1    abcd   2017     300
    1a    bcde   null      Null
    2a cdef   null      Null
Thanks,
Naveen.
Every thought is a cause and every condition an effect

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

You need to CROSS APPLY with a new construct that has each year you want data for. VALUES would be what I use. Your @tax table will never give you the results you desire (specifically at least 1a bcde NULL) because you won't have anything in your query that says "make darn sure I have 2 rows, one for 2016 and one for 2017" for every displayfield row.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

drew.allen SSC Guru Points: 76996 More actions · Answer 2

Here is one option. I initially tried an approach similar to Kevin's, but this seems to require fewer scans.

SELECT df.LineNumber, df.MFN, MAX(CASE WHEN df.MFN = t.MFN THEN t.TaxYear END) AS TaxYear FROM @DisplayFields df CROSS JOIN @tax t GROUP BY df.LineNumber, df.MFN, t.TaxYear ORDER BY t.TaxYear, df.LineNumber

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Naidu PK SSCrazy Points: 2481 More actions · Answer 3

drew.allen - Friday, January 26, 2018 2:20 PM
Here is one option. I initially tried an approach similar to Kevin's, but this seems to require fewer scans.
SELECT df.LineNumber, df.MFN, MAX(CASE WHEN df.MFN = t.MFN THEN t.TaxYear END) AS TaxYear FROM @DisplayFields df CROSS JOIN @tax t GROUP BY df.LineNumber, df.MFN, t.TaxYear ORDER BY t.TaxYear, df.LineNumber
Drew

I have more fields in @tax table. Group by might not work in that case. I updated the original query.

Thanks,
Naveen.
Every thought is a cause and every condition an effect

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

drew.allen - Friday, January 26, 2018 2:20 PM
Here is one option. I initially tried an approach similar to Kevin's, but this seems to require fewer scans.
SELECT df.LineNumber, df.MFN, MAX(CASE WHEN df.MFN = t.MFN THEN t.TaxYear END) AS TaxYear FROM @DisplayFields df CROSS JOIN @tax t GROUP BY df.LineNumber, df.MFN, t.TaxYear ORDER BY t.TaxYear, df.LineNumber
Drew

Will this solution work when he needs output for 2016, 2017 and 2018 but doesn't happen to have any 2018 rows in the @tax table?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

Naveen PK - Friday, January 26, 2018 1:54 PM
Updated Query tables:
I have a table called DisplayFields and another one called tax.
Displayfields table has all unique rows (master table). I want all the rows from DisplayFields table to each tax year whether it matches or not with MFN columns. In essence, the 3 rows in DisplayFields is repeated for each tax year.
I tried cross join but struggling to filter out the unwanted data.
DECLARE @DisplayFields as TABLE     (                  [LineNumber] nvarchar(250),         MFN nvarchar(500)     )     Insert @DisplayFields ( [LineNumber],MFN)     Values ( '1','abcd' ), ('1a', 'bcde'), ('2a', 'cdef')
   declare @tax as table     (         MFN nvarchar(500),         TaxYear Int     )     insert @tax (MFN, TaxYear)     Values ('abcd',    2016), ('bcde'    ,2016), ('abcd'    ,2017)
Desired output:
LineNumber MFN TaxYear Value
   1 abcd   2016      100
    1a    bcde 2016     200
    2a    cdef null       Null
    1    abcd   2017     300
    1a    bcde   null      Null
    2a cdef   null      Null

Hold the phone a minute... Your desired output contains a column called "Value" but isn't contained in either of the two tables. Are you saying that you simply want the value of the "Value" column to contain a count starting at 100 and incrementing by 100 where the MFN of the DisplayFields table has no match in the Tax table?

I just want to make sure that's what you want to do because, not knowing anything about the overriding process behind all of this, it makes no sense.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Naidu PK SSCrazy Points: 2481 More actions · Answer 6

Jeff Moden - Saturday, January 27, 2018 10:41 AM
Naveen PK - Friday, January 26, 2018 1:54 PM
Updated Query tables:
I have a table called DisplayFields and another one called tax.
Displayfields table has all unique rows (master table). I want all the rows from DisplayFields table to each tax year whether it matches or not with MFN columns. In essence, the 3 rows in DisplayFields is repeated for each tax year.
I tried cross join but struggling to filter out the unwanted data.
DECLARE @DisplayFields as TABLE     (                  [LineNumber] nvarchar(250),         MFN nvarchar(500)     )     Insert @DisplayFields ( [LineNumber],MFN)     Values ( '1','abcd' ), ('1a', 'bcde'), ('2a', 'cdef')
   declare @tax as table     (         MFN nvarchar(500),         TaxYear Int     )     insert @tax (MFN, TaxYear)     Values ('abcd',    2016), ('bcde'    ,2016), ('abcd'    ,2017)
Desired output:
LineNumber MFN TaxYear Value
   1 abcd   2016      100
    1a    bcde 2016     200
    2a    cdef null       Null
    1    abcd   2017     300
    1a    bcde   null      Null
    2a cdef   null      Null
Hold the phone a minute... Your desired output contains a column called "Value" but isn't contained in either of the two tables. Are you saying that you simply want the value of the "Value" column to contain a count starting at 100 and incrementing by 100 where the MFN of the DisplayFields table has no match in the Tax table?
I just want to make sure that's what you want to do because, not knowing anything about the overriding process behind all of this, it makes no sense.

Sorry Jeff, that was a copy paste error. I added Value column now. In fact, the real case has multiple value columns (like value1, value2, etc)

Thanks,
Naveen.
Every thought is a cause and every condition an effect

adish SSChasing Mays Points: 634 More actions · Answer 7

Try this:
SELECT
        DF.LineNumber
        ,DF.MFN
        ,Years.TaxYear
        ,Tax.Value
    FROM
        @DisplayFields DF
        CROSS JOIN (SELECT TaxYear FROM @tax GROUP BY TaxYear) Years
        LEFT OUTER JOIN @tax Tax ON DF.MFN = Tax.MFN AND Years.TaxYear = Tax.TaxYear
    ORDER BY
        Years.TaxYear
        ,DF.LineNumber

drew.allen SSC Guru Points: 76996 More actions · Answer 8

TheSQLGuru - Saturday, January 27, 2018 9:47 AM
drew.allen - Friday, January 26, 2018 2:20 PM
Here is one option. I initially tried an approach similar to Kevin's, but this seems to require fewer scans.
SELECT df.LineNumber, df.MFN, MAX(CASE WHEN df.MFN = t.MFN THEN t.TaxYear END) AS TaxYear FROM @DisplayFields df CROSS JOIN @tax t GROUP BY df.LineNumber, df.MFN, t.TaxYear ORDER BY t.TaxYear, df.LineNumber
Drew
Will this solution work when he needs output for 2016, 2017 and 2018 but doesn't happen to have any 2018 rows in the @tax table?

Where does the OP state that this is a requirement?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

drew.allen SSC Guru Points: 76996 More actions · Answer 9

Naveen PK - Friday, January 26, 2018 5:01 PM
drew.allen - Friday, January 26, 2018 2:20 PM
Here is one option. I initially tried an approach similar to Kevin's, but this seems to require fewer scans.
SELECT df.LineNumber, df.MFN, MAX(CASE WHEN df.MFN = t.MFN THEN t.TaxYear END) AS TaxYear FROM @DisplayFields df CROSS JOIN @tax t GROUP BY df.LineNumber, df.MFN, t.TaxYear ORDER BY t.TaxYear, df.LineNumber
Drew
I have more fields in @tax table. Group by might not work in that case. I updated the original query.

It will still work with your sample data.

SELECT df.LineNumber, df.MFN, MAX(CASE WHEN df.MFN = t.MFN THEN t.TaxYear END) AS TaxYear, MAX(CASE WHEN df.MFN = t.MFN THEN t.[Value] END) AS [Value] FROM @DisplayFields df CROSS JOIN @tax t GROUP BY df.LineNumber, df.MFN, t.TaxYear ORDER BY t.TaxYear, df.LineNumber

If you have a large number of fields, this might be a better approach, but it's essentially the same.
SELECT df.LineNumber, df.MFN, MAX(v.TaxYear) AS TaxYear, MAX(v.[Value]) AS [Value] FROM @DisplayFields df CROSS JOIN @tax t OUTER APPLY ( SELECT TaxYear, [Value] WHERE df.MFN = t.MFN ) v GROUP BY df.LineNumber, df.MFN, t.TaxYear ORDER BY t.TaxYear, df.LineNumber

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Naidu PK SSCrazy Points: 2481 More actions · Answer 10

adish - Saturday, January 27, 2018 7:19 PM
Try this:
SELECT
        DF.LineNumber
        ,DF.MFN
        ,Years.TaxYear
        ,Tax.Value
    FROM
        @DisplayFields DF
        CROSS JOIN (SELECT TaxYear FROM @tax GROUP BY TaxYear) Years
        LEFT OUTER JOIN @tax Tax ON DF.MFN = Tax.MFN AND Years.TaxYear = Tax.TaxYear
    ORDER BY
        Years.TaxYear
        ,DF.LineNumber

This worked for me. Thanks Adish

Thanks,
Naveen.
Every thought is a cause and every condition an effect