CODEWITHSUNDEEP
sqlsql-server-2008pivot
Mohammad Adibi
Mohammad Adibi

Reputation: 19

SQL select to see rows in columns

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.

Upvotes: 1

Views: 124

Answers (3)

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11609

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

Upvotes: 1

Philip Sheard
Philip Sheard

Reputation: 5825

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.

Upvotes: 0

Tarik
Tarik

Reputation: 11209

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

Upvotes: 2

Related Questions