allocate values to different section t-sql

Question

allocate values to different section t-sql

srk 41

Hi there,
I would like to allocate values to different communication types. The main intension is I need to fill up the phone numbers for 4 columns. If object Id got 3 phone numbers under one communication type and 2 under another communication type , need to fill up the columns first with relevant communication type and left over numbers assigned to other columns

please see my input and required output

Please help me on this

Yitzhak Khabinsky 27,196 Reputation points

2022-03-20T01:03:50.33+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2022-03-20T11:04:15.663+00:00

So what are the exact rules to select the overflow numbers? That is , why have you selected the two numbers starting in 201 for Comm_79 and Comm_87 and not the other numbers? Does the Rank column mean anything?

This looks like a non-trivial problem, so it certainly helps to have the exact rules, so that we don't waste our time on the wrong thing.

Also, as Yitzhak said, please provide CREATE TABLE + INSERT so that we have something to work with. I would also like to see a few more rows with different ObjectID, so we can see - and test - different cases.

I don't know if the numbers in the screenshot are real numbers or just mockups, but I note that the numbers you place in Comm_83 and Comm_88 seem to be the same number, but one has an extraneous 0.
Bert Zhou-msft 3,521 Reputation points

2022-03-21T02:12:45.333+00:00

Hi，@srk

Welcome to Microsoft T-SQL Q&A Forum!

I would like to understand a few questions, firstly, from the table you posted you put two communication types83 and 88.
For example, when the communication type is 88, there are 4 phone numbers. Why is the expected result at this time 44783173 instead of 2017022216? Secondly, you need to fill in the column with the relevant communication type. You need to explain here, if I can't get the test Data, these theories cannot be implemented for the time being.

Best regards,
Bert Zhou