SQL select to see rows in columns

Question

I have this table:

id   owner   model  
1    1       circle  
2    1       rectangle  
3    1       squire 
4    1       pyramid
5    2       apple  
6    2       orange   
8    3       circle  
9    3       rectangle  
10   3       star

and I need a select to see:

owner   model 1    model 2       model 3     model 4
1       circle     rectangle     squire      pyramid
2       apple      orange          
3       circle     rectangle     star

please help.

score 2 · Answer 1 · answered Oct 05 '13 at 05:56

2

You are looking for a crosstab query. See http://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/ for a tutorial.

answered Oct 05 '13 at 05:56

Tarik

10,810
2
26
40

1

if i say correct, crosstab query is about sum rows, but here i have strings. – Mohammad Adibi Oct 05 '13 at 06:32
I overlooked that and my answer is therefore incorrect. SQL will simply not do what you want. You have to write a stored procedure. – Tarik Oct 05 '13 at 06:59

Prahalad Gaggar · Answer 2 · 2013-10-05T07:30:42.643

Taking assumptions,that each owner for each group will have 3 rows,

If that's not the case then i have to write a SP (can't do right now will help you later on)

select [owner], [group], 
    parsename(Model,3) as Model1
    ,parsename(Model,2) as Model1
    ,parsename(Model,1) as Model1
from
(
select [owner], [group],
STUFF((
    select '.' + model
    from Table1 t2 where 
    t2.[owner]=t1.[owner]
    and t2.[group]=t1.[group]
    for xml path('')),1,1,'') Model
from Table1 t1
group by [owner],[group]
)t

SQl Fiddle

Update (As per OP request)

select [owner]
    ,case len(model)-len(replace(model,'.',''))
        when 3 then parsename(replace(model,'.'+reverse(substring(reverse(model),1,charindex('.',model,1))),''),3)
        when 2 then parsename(Model,3)
        when 1 then parsename(Model,2)
        else Model 
    end as Model1
    ,case len(model)-len(replace(model,'.',''))
        when 3 then parsename(replace(model,'.'+reverse(substring(reverse(model),1,charindex('.',model,1))),''),2)
        when 2 then parsename(Model,2)
        when 1 then parsename(Model,1)
        else ''
    end as Model2
    ,case len(model)-len(replace(model,'.',''))
        when 3 then parsename(replace(model,'.'+reverse(substring(reverse(model),1,charindex('.',model,1))),''),1)
        when 2 then parsename(Model,1)
        else ''
    end as Model3
    ,case len(model)-len(replace(model,'.',''))
        when 3 then reverse(substring(reverse(model),1,charindex('.',model,1)))
        else ''
    end as Model4
from
(
select [owner], 
    STUFF((
    select '.' + model
    from Table1 t2 where 
    t2.[owner]=t1.[owner]
    for xml path('')),1,1,'') Model
from Table1 t1
group by [owner]
)t

SQl Fiddle

thanks. what can i do if each owner has difference group quantity selected. here each of them has 3 select, but in real it cab be different. — Mohammad Adibi, Oct 05 '13 at 06:27
If your system permits user defined function we can do it more easily. — Prahalad Gaggar, Oct 05 '13 at 10:05

score 0 · Answer 3 · answered Oct 05 '13 at 07:00

0

What you are presenting as a result table is not a table at all. It is not a crosstab either, though it looks a bit similar. What you are really looking for is an aggregate function that concatenates strings, but unfortunately none exists. The only solution would be to create a user defined one, using T-SQL. But that is not for the faint-hearted.

answered Oct 05 '13 at 07:00

Philip Sheard

5,789
5
27
42

it quite easy. Just have look at my anwser. – Prahalad Gaggar Oct 05 '13 at 10:06
Well done. Now give us the solution for an owner with 100 different models. – Philip Sheard Oct 05 '13 at 13:26
i just need a **[user defined split function](http://stackoverflow.com/questions/2647/split-string-in-sql)** – Prahalad Gaggar Oct 05 '13 at 13:36
I think you mean a join function. But my main point is that the output that the OP wants is not in true tabular format, and therefore not possible with standard SQL. All you have done is to solve a special case. – Philip Sheard Oct 05 '13 at 16:09

SQL select to see rows in columns

3 Answers3