0

I'm trying to make a query where multiple contacts (stored in the same table) can be generated as

Contacts.strSurname AS [Person 1], 
Contacts.strSurname AS [Person 2],
Contacts.strSurname AS [Person 3] etc

and be output in the same row.

I have already offered the below solution but I need to make all these contacts be in the same row

Table.TablePK AS Unique, 
CONCAT('person No: ', ROW_NUMBER() OVER (PARTITION BY Table.TablePK 
                                         ORDER BY Contacts.strSurname) + 0) AS [Person No]

Any help would be greatly appreciated as any solution it try I can't get to work

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Wigmoir
  • 1
  • 1
    Welcome to Stack Overflow. Your question isn't completely clear -- you could [edit] it to give an example. In the meantime check out https://learn.microsoft.com/en-gb/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017 and https://stackoverflow.com/questions/46661496/how-to-use-group-concat-function-on-mssql . – O. Jones Dec 17 '21 at 10:14
  • It's not entirely clear to me what exact end result you do want, but I'm assuming you're asking about [converting rows to columns](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server). The `row_number() over partition by` won't do that since you told it to group ("partition") your results by the table's PK (1 result per 1 PK => 1 result row per 1 table row). You can use this to get person numbers but you'll still have to switch rows to columns after. – Arie Dec 17 '21 at 10:37
  • 1
    Hello, please give more details about inputs (at least table structures) and objectives, what you tried so far (complete queries) with related results compared to expected ones. Please read [How to ask](https://stackoverflow.com/help/how-to-ask) and especially [how to create a reproductible example](https://stackoverflow.com/help/minimal-reproducible-example) – Christophe Dec 17 '21 at 11:06

0 Answers0