CODEWITHSUNDEEP
sqlsql-server
VANDANA PANDEY
VANDANA PANDEY

Reputation: 23

how to convert 1 column value into multiple column

Suppose if i have one table "Title" In this table there are 4 columns.

Bookid, Isbn, TitleName, Description.

1         123  english     this is a english buk.

2        123  english      Its author is "Mr xyz"

Now you see that b'coz of description same book appear 2 times.

Now how can we get the below output.Plese Guide

Bookid, Isbn,         TitleName,           Description1,                  Description2

1         123         english              "this is a english buk"         "Its author is Mr xyz"

Upvotes: 1

Views: 450

Answers (4)

Deepak Mishra
Deepak Mishra

Reputation: 3183

@bluefeet, I modified your query to allow more than 10 descriptions (sorted)....

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((select c from (SELECT  ',' + QUOTENAME('Description'+cast(row_number() over (partition by bookid, isbn, titlename order by bookid) as varchar(11))) AS C
                    from title) TBL group by c order by CAST(Replace(REPLACE(C,',[Description',''),']','') as int)
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
      = 'SELECT bookid, isbn, titlename,' + @cols + ' from 
         (
            select bookid, isbn, titlename, description,
              ''Description''+ cast(row_number() over (partition by bookid, isbn, titlename order by bookid) as varchar(10)) rn
            from title
         ) x
         pivot 
         (
            min(description)
            for rn in(' + @cols + ')
         ) p '

execute(@query)

Upvotes: 0

Taryn
Taryn

Reputation: 247700

Based on your comments, it sounds like you want a dynamic way of producing these results. You can use Dynamic SQL to generate a PIVOT. Using dynamic SQL will allow you to have additional descriptions automatically added to your results.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Description'+cast(row_number() over (partition by bookid, isbn, titlename order by bookid) as varchar(10))) 
                    from title
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
      = 'SELECT bookid, isbn, titlename,' + @cols + ' from 
         (
            select bookid, isbn, titlename, description,
              ''Description''+ cast(row_number() over (partition by bookid, isbn, titlename order by bookid) as varchar(10)) rn
            from title
         ) x
         pivot 
         (
            min(description)
            for rn in(' + @cols + ')
         ) p '

execute(@query)

See SQL Fiddle with Demo.

As you add new descriptions for the books, a new column will be added to the results when you execute the query.

Upvotes: 2

podiluska
podiluska

Reputation: 51494

Try this

 select * 
 from
 (
      select 
           *,
           row_number() over (partition by bookid, isbn, titlename order by description) rn
      from yourtable
 ) src
 pivot
 ( max(description) for rn in ([1],[2],[3])) p

Upvotes: 0

Andomar
Andomar

Reputation: 238086

You could use a subquery to calculate the minimum and maximum BookId per book. Then you can join the two descriptions:

select  min(BookId)
,       Isdn
,       TitleName
,       max(case when rn = 1 then Description end) as Descr1
,       max(case when rn = 2 then Description end) as Descr2
,       max(case when rn = 3 then Description end) as Descr3
from    (
        select  row_number() over (partition by Isdn, TitleName order by BookId) as rn
        ,       BookId
        ,       Isdn
        ,       TitleName
        ,       Description 
        from    Title
        ) as ids
group by
        Isdn
,       TitleName

Upvotes: 1

Related Questions