Return rows as columns in SQL Server query

Do you want separate columns or one string with all the names (yuck)?

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...

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...

Thanks, guys.  I want separate columns.

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

Dale

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;

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

Select allOpen in new window

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

Select allOpen in new window

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?

Disregard, I think I resolved the problem by changing:

   , ROW_NUMBER() OVER(PARTITION BY C.FamilyID ORDER BY C.ChildID) AS row_num 

Select allOpen in new window

to:

   , ROW_NUMBER() OVER(PARTITION BY R.ReferralID, R.FamilyID ORDER BY C.ChildID) AS row_num 

Select allOpen in new window

did you try my solution?

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.

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.

Thanks for your help.

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...

@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.

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