How to take a value from a column in SQL Server and repeat the values in a different column?

Question

I have a table which has following columns

Question                             Answer
What is your role?                   Leader
what is your qualification?          MBA
what training did you go through?    SQL Server
Who is your manager?                 XYZ
What is your role?                   Supervisor
what is your qualification?          BS
what training did you go through?    Excel
Who is your manager?                 ABC

I want to create another column which would take the value in the "Answer" column corresponding to "What is your role?" in the Question column and repeat for all the questions in the table. So it should look something like as below.

Result:

Question                             Answer         ColumnX
What is your role?                   Leader         Leader
what is your qualification?          MBA            Leader
what training did you go through?    SQL Server     Leader
Who is your manager?                 XYZ            Leader
What is your role?                   Supervisor     Supervisor
what is your qualification?          BS             Supervisor
what training did you go through?    Excel          Supervisor
Who is your manager?                 ABC            Supervisor

There is no way with the sample data you have shown, to do this. There is nothing connecting the `Role` answer with the answers to any of the other questions. I'd post this as the answer, but I suspect there must be other columns in the table that you haven't shown that might help to do what you want. — Tab Alleman, Jun 07 '18 at 13:19
You need at least an order to link the role answer to the following ones. — EzLo, Jun 07 '18 at 13:20
How do you determine which rows / questions get updated with the value? — junkangli, Jun 07 '18 at 13:20
You appear to be repeating the question multiple times - once for every possible answer to that question. It's not an efficient data structure and doesn't make use of the power of relational databases. Have one table for questions, and one table for answers. Each answer has a question ID next to it, which is a foreign key ID to the question it is linked with. Then you can define each question once, but define many possible answers. Then define some roles in a 3rd table, and again in the Answers table have a RoleID column which relates the answer to the role. — ADyson, Jun 07 '18 at 13:22
@NikhilSahni This will be easier if the table has an ID (identifier), or a some datetime that the record was created. Because just because you present those lines in that certain order, doesn't mean the database think they are in that order. Basically, a solution needs some field to sort it on. — LukStorms, Jun 07 '18 at 13:34

LukStorms · Answer 1 · 2018-06-07T14:25:02.393

Assuming that your table has a unique identifier, then we can order by that.

If your SQL Server can use the window function FIRST_VALUE (2012+)

Then one of the below methods can give those results.

--
-- Using a temporary table for testing purposes
--
if object_id('tempdb..#tmpTestAnswers') is not null drop table #tmpTestAnswers;
create table #tmpTestAnswers (id int identity(1,1) primary key, Question varchar(100), Answer varchar(30));
--
-- Sample data
--
insert into #tmpTestAnswers (Question, Answer) values
 ('What is your role?', 'Leader')
,('what is your qualification?', 'MBA')
,('what training did you go through?', 'SQL Server')
,('Who is your manager?', 'XYZ')
,('What is your role?', 'Supervisor')
,('what is your qualification?', 'BS')
,('what training did you go through?', 'Excel')
,('Who is your manager?', 'ABC');

Method 1:

--  
-- Using a cummulative sum to get a ranking, 
-- Then get the role via a first_value. 
-- This assumes that the role question is always the first one
--
select Question, Answer, 
 first_value(Answer) over (partition by QuestionRank order by id) as ColumnX
from
(
    select * , sum(iif(Question like '%your role?%',1,0)) over (order by id) as QuestionRank
    from #tmpTestAnswers
) as q
order by id;

Method 2:

-- 
-- If the text in the same questions is consistent, then a row_number that's partitioned by Question will give a ranking
--
select Question, Answer, 
 max(case when Question like '%your role?%' then Answer end) over (partition by QuestionRank order by id) as ColumnX
from
(
    select * , row_number() over (partition by Question order by id) as QuestionRank
    from #tmpTestAnswers
) as q
order by id;

Yogesh Sharma · Answer 2 · 2018-06-07T18:06:01.010

0

You can use cte :

with t as (
      select *, row_number() over (order by (select 1)) as seq
      from table 
   )

select *,(select top 1 t1.Answer
          from t t1
          where t1.seq <= t.seq and t1.question = 'What is your role?'
          order by t1.seq desc
          ) as ColumnX
from t;

However, i used order by (select 1) in row_number() function but you can use your identity column if any.

edited Jun 07 '18 at 18:06

answered Jun 07 '18 at 16:06

Yogesh Sharma

49,870
5
26
52

How to take a value from a column in SQL Server and repeat the values in a different column?

2 Answers2