Is it possible to convert rows to a variable number of columns in T-SQL?

Question

I have a strange requirement where I need to convert a variable amount of rows into columns. I need help figuring out if this could be done in SQL, or if I should just write a program in a different language.

Let's assume I have a table Clients, which only holds minimal client data. Then, a table called Attributes, which names the different possible attributes (say, Phone number, address, etc). Finally, I have the third table, ClientAttributes which holds the two FKs and the value.

So for each client I have any number of attributes. A client can have zero, 1 or infinite phone numbers, zero, 1, or infinite addresses, and so on.

What I need is a table view of all that data. Client Name, Phone, Phone 2, Phone 3, ..., Address, Address 2, Address 3.... and so on. If a client has no such value, the value will be null. Obviously this means that the number of columns may be different every time the query is executed.

This needs to be compatible as far back as SQL Server 2008.

Could this be done purely in T-SQL, or should do this client-side by just dumping the data and let C# handle it? I could easily do it in C# but I'm not sure this could be done in SQL. SQL would be preferred because the dataset may be too large to fit in RAM.

It's probably easier to do in a different language, but it can be done in SQL by building up a string programatically, which contains the dynamic SQL that you wish to execute. Dynamic SQL is a fairly big security no-no, and performance wise it really sucks. But, it can be done. If you would like an example please provide source data and desired results in your question. — Jeff Breadner
– Jeff Breadner, Commented Jul 6, 2018 at 0:00
Search for examples of SQL pivot... Plenty of examples out there, but with the variable number of columns, I'd probably do it in c#. — Tanner
– Tanner, Commented Jul 6, 2018 at 0:04
@JeffBreadner I hate dynamic sql, but the company is in love with it. I'll try to expand my question tomorrow. — hjf
– hjf, Commented Jul 6, 2018 at 0:04
@Tanner Can you use pivot effectively when you have x number of columns needed based on the one user with the max value in that field, and on down the line? — Ryan Wilson
– Ryan Wilson, Commented Jul 6, 2018 at 0:06
Yes it's important to understand the use case. Sometimes it's easily solved by showing end users the magic of excel pivot tables — Nick.Mc
– Nick.Mc, Commented Jul 6, 2018 at 0:35

DancingFool · Accepted Answer · 2018-07-06 04:52:25Z

This can be done in SQL through dynamic sql, if you need to. The basic theory for doing it for one item (I'll use phone) is as follows, you'd repeat this for each other grouping of columns you want. Note that no one will ever say that it is pretty.

Create a base query (CTE, TEMP table, whatever) that gets Client ID, Phone for every valid phone number. Add a rownumber "ROW_NUMBER() OVER (PARTITION BY ClientID ORDER BY (whatever))" - I'll call it basedata
Get the max row_number from basedata - this is the number of phone columns you need

Make parts of a dynamic SQL query string by looping from i = 1 to MaxRowNo. In each loop, you build up a selection string, and a join string. The selection string should add something like the following in each loop

Set @SelectStr = @SelectStr + 'P' + cast(i as varchar(10)) + '.Phone,';

The join string should add something like this in each loop

Set @JoinStr = @JoinStr + ' left outer join baseData P' + cast(i as varchar(10)) + ' on P' + cast(i as varchar(10)) + '.ClientID = C.ClientID and P' + cast(i as varchar(10)) + '.RowNo = ' + cast(i as varchar(10));

You would repeat the whole above process for addresses, and any other repeating groups of columns - make sure you don't double up on alias names. Then you would make up your final dynamic sql query by adding any fixed, unchanging parts of the query (client data), something like this

Set @FinalQuery = 'SELECT C.ClientID, C.ClientName, ' + @SelectStr + ' From Client C ' + @JoinStr

Your final built up query (assuming there is max three phones and two addresses as an example) would look something like this - then EXEC this string

SELECT C.ClientID, C.ClientName, --any other client stuff you need here
   P1.Phone, P2.Phone, P3.Phone, A1.Address, A2.Address
From Client C
   left outer join baseData P1 on P1.ClientID = C.ClientID and P1.RowNo = 1
   left outer join baseData P2 on P2.ClientID = C.ClientID and P2.RowNo = 2
   left outer join baseData P3 on P3.ClientID = C.ClientID and P3.RowNo = 3
   left outer join baseAddr A1 on A1.ClientID = C.ClientID and A1.RowNo = 1
   left outer join baseAddr A2 on A2.ClientID = C.ClientID and A2.RowNo = 2

Collectives™ on Stack Overflow

Is it possible to convert rows to a variable number of columns in T-SQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related