We help IT Professionals succeed at work.
Troubleshooting Question

Return rows as columns in SQL Server query

Dale Fye
Dale Fye asked
on
25 Views
Last Modified: 2020-11-03
I have two tables (tblReferrals and tblReferralChildren).  I'm only going to include the pertinent fields in this question.
tblReferrals contains ReferralID (integer, not null, Identity, PK)
tblReferralChildren contains:
-   ReferralChildID (integer, not null, Identity, PK)
-   ReferralID (integer, FK to tblReferrals!ReferralID)
-   ChildLastName - nvarchar(50)
-   ChildFirstName - nvarchar(25)
There could be any number of children associated with a particular referral, but I'm only interested in the first 7.  I would like to generate a query that returns the names like:
ReferralID   Child1FullName   Child2FullName  Child3FullName  ... Child7FullName
where the sort order of the children (1-7) is based on the sequence of ReferralChildID.
Comment
Watch Question

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Do you want separate columns or one string with all the names (yuck)?
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
is this query returns what you want?

with r as (
select p.*, row_number() over (partition by ReferralID order by ReferralChildID) rn
from parent p
inner join child c on p.id=c.pid
)
select * from r
where rn<=7
order by ReferralID, ReferralChildID

I mean at least records...
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
question editor is messed up...
I see strange numbers in the code...
but if you click "select all" and copy, it works fine...
EE should change this messy editor or update it...
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Thanks, guys.  I want separate columns.

I'll take a look at both of these this afternoon.

Dale
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Similar to Scott's but I'll post it anyway.

WITH TOPX AS (
    SELECT *, ROW_NUMBER()
    OVER (
        PARTITION BY tblReferralChildren.ReferralID
        ORDER BY tblReferralChildren.ReferralChildID
    ) AS ChildNo
    FROM tblReferralChildren
)
SELECT ReferralID,
      MAX(CASE WHEN ChildNo=1 THEN ChildFirstName + ' ' + childLastName END) AS Child1,
      MAX(CASE WHEN ChildNo=2 THEN ChildFirstName + ' ' + childLastName END) AS Child2,
      MAX(CASE WHEN ChildNo=3 THEN ChildFirstName + ' ' + childLastName END) AS Child3,
      MAX(CASE WHEN ChildNo=4 THEN ChildFirstName + ' ' + childLastName END) AS Child4,
      MAX(CASE WHEN ChildNo=5 THEN ChildFirstName + ' ' + childLastName END) AS Child5,
      MAX(CASE WHEN ChildNo=6 THEN ChildFirstName + ' ' + childLastName END) AS Child6,
      MAX(CASE WHEN ChildNo=7 THEN ChildFirstName + ' ' + childLastName END) AS Child7
      
FROM TOPX WHERE ChildNo<=7
GROUP BY ReferralID;
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
with t as (
 select r.ReferralID, c.ReferralChildID, c.ChildFirstName + ' ' + c.ChildLastName ChildFullName,
        row_number() over (partition by r.ReferralID order by ReferralChildID) rn
   from tblReferrals r
  inner join tblReferralChildren c on c.ReferralID=r.ReferralID
)
select ReferralID,
       max(case when rn=1 then ChildFullName end) ChildFullName1,
       max(case when rn=2 then ChildFullName end) ChildFullName2,
       max(case when rn=3 then ChildFullName end) ChildFullName3,
       max(case when rn=4 then ChildFullName end) ChildFullName4,
       max(case when rn=5 then ChildFullName end) ChildFullName5,
       max(case when rn=6 then ChildFullName end) ChildFullName6,
       max(case when rn=7 then ChildFullName end) ChildFullName7
  from t
 where rn<=7
 group by ReferralID
 order by ReferralID

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
OK, I had to include a couple of additional tables, and to get a couple of additional fields to make it easier to review the results of the query against the actual tables.  Here is my current query:
declare @programID as int = 2
declare @CountyID as int = 0
declare @RptStartDate as date = '2020-10-01'
declare @RptEndDate as date = '2020-10-31'


SELECT ReferralID,
   ReferralName,
   ProgramName,
   CountyName,  
   MAX(CASE WHEN row_num = 1 THEN LastName + ', ' + FirstName END) AS Child1FullName,
   MAX(CASE WHEN row_num = 2 THEN LastName + ', ' + FirstName END) AS Child2FullName,
   MAX(CASE WHEN row_num = 3 THEN LastName + ', ' + FirstName END) AS Child3FullName,
   MAX(CASE WHEN row_num = 4 THEN LastName + ', ' + FirstName END) AS Child4FullName,
   MAX(CASE WHEN row_num = 5 THEN LastName + ', ' + FirstName END) AS Child5FullName,
   MAX(CASE WHEN row_num = 6 THEN LastName + ', ' + FirstName END) AS Child6FullName,
   MAX(CASE WHEN row_num = 7 THEN LastName + ', ' + FirstName END) AS Child7FullName
FROM (
    SELECT R.ReferralID
   , R.ReferralName
   , P.ProgramName
   , SC.CountyName
   , C.FirstName
   , C.Lastname
   , ROW_NUMBER() OVER(PARTITION BY C.FamilyID ORDER BY C.ChildID) AS row_num
    FROM tblFPReferrals as R LEFT JOIN (
   SELECT * FROM tblFPChildren
   WHERE (RTrim(isnull(FirstName, '')) <> '')
   AND (RTrim(isnull(LastName, '')) <> '')
   ) as C ON R.FamilyID = C.FamilyID INNER JOIN
   tblFPPrograms as P on R.ProgramID = P.ProgramID INNER JOIN
   tblStateCounties  as SC on R.CountyID = SC.tblCountyID
   WHERE (R.ProgramID = @ProgramID OR @ProgramID = 0)
   AND (R.CountyID = @CountyID or @CountyID = 0)
   AND (R.ReferralDate <= @RptEndDate)
   AND (R.ClosingDate IS NULL or R.ClosingDate >= @RptStartDate)
) AS derived
WHERE row_num BETWEEN 1 AND 7
GROUP BY ReferralID, ReferralName, ProgramName, CountyName
But for some records, this is returning strange results where Child1FullName is NULL, but Child2FullName contains a value. In some cases Child2FullName is NULL, but Child3FullName contains a name.  I thought this might be resolved with the SubQuery on tblFPChildren fo filter out records where both the FirstName and LastName must contain something, but that does not seem to have resolved the issue.
Any idea what would cause these NULL values to show up in the query results, between other child names?

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Disregard, I think I resolved the problem by changing:
   , ROW_NUMBER() OVER(PARTITION BY C.FamilyID ORDER BY C.ChildID) AS row_num 
to:
   , ROW_NUMBER() OVER(PARTITION BY R.ReferralID, R.FamilyID ORDER BY C.ChildID) AS row_num 
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
did you try my solution?
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Also, this code doesn't look right:

WHERE (RTrim(isnull(FirstName, '')) <> '')
   AND (RTrim(isnull(LastName, '')) <> '')

That would include NULL values for FirstName and/or LastName, is that really what you want??

WHERE FirstName <> '' AND LastName <> ''

is clearer and cleaner.  NULL values will not be included.

Note that trailing spaces match strings w/o trailing spaces, for example:
SELECT CASE WHEN SPACE(5) = SPACE(0) THEN 'Matches' ELSE 'Doesn''t match' END
so there's not need to use RTRIM.



Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Kurt,

I tried your first one, but then when I tried to filter it with parameters within the CTE, I got an error, so I used Scott's.

I've got another question which addresses using parameters within a CTE.  If that doesn't work, I'll just have to make each of these queries that I'm running as sub-queries within a much larger query.

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Thanks for your help.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
if you are getting NULL
then it may be because first or lastname may be null

then use

c.ChildFirstName + ' ' + c.ChildLastName 
>>>
trim(isnull(c.ChildFirstName,'') + ' ' + isNull(c.ChildLastName,''))

in the query I posted...
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
@Scott,
My intent with that WHERE clause was to exclude records from tblFPChildren where both were NULL or zero length strings.  In Access, trailing spaces do not compare as a match "A  " <> "A     ", although you rarely see that any more.  I also did not realize that [FirstName] <> '' would filter out records were [FirstName] is NULL.  Again, Access requires us to account for Null or blank, so NZ([FirstName], '') <> '' filters out records where [FirstName] is null or blank.

@HainKurt, I resolved the NULLs by including the R.ReferralID and R.Family ID in the "Partition" clause.  I realized I needed this when ran a query on the two tables and found that there can be multiple referrals linked to the same Family.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Ah, I wasn't aware of that about Access.

In SQL Server, NULL is never "=" or "<>" anything, even another NULL;

select case when col1 = col2 then 'nulls match' else 'nulls don''t match' end
from (
select cast(null as int) as col1, cast(null as int) as col2
) as test