1

I have table like below:

Tname         Scode
-----         -----
SVC           code1
SVC           code2
SVC           code3
AKV           code1
AKV           code12
AKV           code3
AKS           code3
AKS           code2
AKS           code1

I would want a result like below:

SVC      AKV         AKS
----     -----       ----
code1    code1       code3
code2    code12      code2
code3    code3       code1
  • What determines which columns end up in which row? – Philᵀᴹ Apr 27 '17 at 7:21
0

You can try this. I think it's usefull, and will fix the problem if you have more codes in a column, I'm just trying to not select the row_number column.

SELECT *
FROM 
    (SELECT 
         Tname,scode,
         rn = ROW_NUMBER() OVER (PARTITION BY Tname ORDER BY Scode) 
     FROM teste_1) AS src
PIVOT (
  MIN(scode)
  FOR Tname IN ([SVC],[AKV],[AKS])
) AS pvt


| rn | SVC   | AKV    | AKS   | 
|----|-------|--------|-------|
| 1  | bola  | code1  | code1 |   
| 2  | code1 | code12 | code2 |   
| 3  | code2 | code3  | code3 |   
| 4  | code3 | NULL   | NULL  |   
  • 1
    Please, do not use images, use some online table generator like this: tablesgenerator.com/markdown_tables# (copy and paste) – McNets Apr 27 '17 at 13:47
  • @McNets - I'm curious as to why you think we should use this markdown stuff in lieu images for representing result set answers like this. We wouldn't really be trying to select the results as text. I'm interested in what you perceive as the benefits of the markdown stuff - I'm willing to learn new stuff, but I want to know why one way is better than another way. – Scott Hodgin Apr 27 '17 at 15:30
  • @ScottHodgin for the same reason that one OP shouldn't use its on a question. Text (tables) will be there for ever. – McNets Apr 27 '17 at 15:47
  • 1
    @mcnets - valid points – Scott Hodgin Apr 27 '17 at 16:12
  • Oh Sure, I didn't know about this tabeGenerator. – Racer SQL Apr 27 '17 at 17:59
0

Use Inner Join to display the three queries horizontally. Then use Row_number for setting join conditions,

As a demo:-

create table #temp (Tname varchar(10) , Scode varchar(10))
insert into #temp values ('SVC','code1')
insert into #temp values ('SVC','code2')
insert into #temp values ('SVC','code3')
insert into #temp values ('AKV','code1')
insert into #temp values ('AKV','code12')
insert into #temp values ('AKV','code3')
insert into #temp values ('AKS','code3')
insert into #temp values ('AKS','code2')
insert into #temp values ('AKS','code1')

go 

SELECT SVC , AKV , AKS
FROM (
    select Scode as SVC , row_number() over(partition by Tname order by (select 1)) rn  
    from #temp where Tname = 'SVC')  t1
INNER JOIN (
    select  Scode as AKV , row_number() over(partition by Tname order by (select 1)) rn 
    from #temp where Tname = 'AKV' )  t2
    on t1.rn = t2.rn
INNER JOIN (
    select  Scode as AKS , row_number() over(partition by Tname order by (select 1)) rn 
    from #temp where Tname = 'AKS' ) t3
    on t2.rn = t3.rn


--drop table #temp

Result:-

| SVC       | AKV       | AKS       |
|-------    |--------   |-------    |
| code2     | code1     | code3     |
| code3     | code12    | code2     |
| code3     | code3     | code1     |
  • But, Let's say I have more Scodes in one of these columns. how can I show it, even if it shows 'null' in the other columns? I'm trying to code it but i can't think in something useful. – Racer SQL Apr 27 '17 at 13:29
  • @RafaelPiccinelli You are right, but I think this is not the case for OP. – ahmed abdelqader Apr 27 '17 at 13:32
  • Oh Yes I know, I was just curious to use your code when Needed haha. – Racer SQL Apr 27 '17 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.