Multiple row data need to be convert as Coloumn

  • HI All,

    I need to convert the Rows data into column for multiple rows in table.

    actual data in my table seems like attached from 01.JPEG. this i need to be convert in form of 02.JPEG.

    Pavan Kumar. Pala[font="Verdana"][/font]
  • Quick answer: concatenate strings using XML path.

    Full answer with script: Only if you provide scripts to create the source data table with data...

    Thomas Rushton
    blog: https://thelonedba.wordpress.com

  • You required comma separated values in single column. Following is a code which demonstrate it:

    Declare @Table table

    (

    ID int,

    Code varchar(10),

    score int

    )

    insert into @Table

    select 1,'aa',44 union all

    select 1,'bb',54 union all

    select 1,'cc',74 union all

    select 2,'bbb',14 union all

    select 2,'ccc',24 union all

    select 2,'ddd',34

    ;

    WITH CTE

    AS

    (

    Select distinct id

    from @Table t

    )

    Select *

    , (select

    stuff(

    (select ', ' + Code + ''

    from @Table tt

    where t.id = tt.ID

    order by id

    for xml path(''), root('MyString'), type

    ).value('/MyString[1]','varchar(max)')

    , 1, 2, '')) as CodeList

    , (select

    stuff(

    (select ', ' + cast(score as nvarchar(10)) + ''

    from @Table tt

    where t.id = tt.ID

    order by id

    for xml path(''), root('MyString'), type

    ).value('/MyString[1]','varchar(max)')

    , 1, 2, '')) as ScoreList

    from CTE t

    hope it helps

  • HI All,

    Please find the sample table structure.

    create table ITGDaillyMetrics (

    ITG varchar (max),

    Backenderror int,

    dates datetime )

    insert into ITGDaillyMetrics values (ITG01 - XFINITY Username and Password Reset,10,26 Sep 2016)

    insert into ITGDaillyMetrics values (ITG02 - XFINITY Username and Password Reset,20,26 Sep 2016)

    insert into ITGDaillyMetrics values (ITG03 - XFINITY Username and Password Reset,30,26 Sep 2016)

    insert into ITGDaillyMetrics values (ITG04 - XFINITY Username and Password Reset,40,26 Sep 2016)

    insert into ITGDaillyMetrics values (ITG05 - XFINITY Username and Password Reset,50,26 Sep 2016)

    insert into ITGDaillyMetrics values (ITG01 - XFINITY Username and Password Reset,10,25 Sep 2016)

    insert into ITGDaillyMetrics values (ITG02 - XFINITY Username and Password Reset,20,25 Sep 2016)

    insert into ITGDaillyMetrics values (ITG03 - XFINITY Username and Password Reset,30,25 Sep 2016)

    insert into ITGDaillyMetrics values (ITG04 - XFINITY Username and Password Reset,40,25 Sep 2016)

    insert into ITGDaillyMetrics values (ITG05 - XFINITY Username and Password Reset,50,25 Sep 2016)

    insert into ITGDaillyMetrics values (ITG01 - XFINITY Username and Password Reset,10,24 Sep 2016)

    insert into ITGDaillyMetrics values (ITG02 - XFINITY Username and Password Reset,20,24 Sep 2016)

    insert into ITGDaillyMetrics values (ITG03 - XFINITY Username and Password Reset,30,24 Sep 2016)

    insert into ITGDaillyMetrics values (ITG04 - XFINITY Username and Password Reset,40,24 Sep 2016)

    insert into ITGDaillyMetrics values (ITG05 - XFINITY Username and Password Reset,50,24 Sep 2016)

    insert into ITGDaillyMetrics values (ITG01 - XFINITY Username and Password Reset,10,23 Sep 2016)

    insert into ITGDaillyMetrics values (ITG02 - XFINITY Username and Password Reset,20,23 Sep 2016)

    insert into ITGDaillyMetrics values (ITG03 - XFINITY Username and Password Reset,30,23 Sep 2016)

    insert into ITGDaillyMetrics values (ITG04 - XFINITY Username and Password Reset,40,23 Sep 2016)

    insert into ITGDaillyMetrics values (ITG05 - XFINITY Username and Password Reset,50,23 Sep 2016)

    Pavan Kumar. Pala[font="Verdana"][/font]
  • You're strings/dates aren't quoted. You should always try to run your query before posting it on the Internet.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • To help others who are trying to help you here is how you should post it in the future. Note I changed your DDL to declare a table variable rather than an actual table (a temp table would also be appropriate) because I'd rather not have these tables sticking around if I forget the delete them afterwards. At the very least just make sure to add a DROP TABLE statement at the end.

    DECLARE @ITGDaillyMetrics TABLE (

    ITG varchar (max),

    Backenderror int,

    dates datetime )

    insert into @ITGDaillyMetrics values ('ITG01 - XFINITY Username and Password Reset',10,'26 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG02 - XFINITY Username and Password Reset',20,'26 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG03 - XFINITY Username and Password Reset',30,'26 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG04 - XFINITY Username and Password Reset',40,'26 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG05 - XFINITY Username and Password Reset',50,'26 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG01 - XFINITY Username and Password Reset',10,'25 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG02 - XFINITY Username and Password Reset',20,'25 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG03 - XFINITY Username and Password Reset',30,'25 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG04 - XFINITY Username and Password Reset',40,'25 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG05 - XFINITY Username and Password Reset',50,'25 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG01 - XFINITY Username and Password Reset',10,'24 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG02 - XFINITY Username and Password Reset',20,'24 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG03 - XFINITY Username and Password Reset',30,'24 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG04 - XFINITY Username and Password Reset',40,'24 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG05 - XFINITY Username and Password Reset',50,'24 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG01 - XFINITY Username and Password Reset',10,'23 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG02 - XFINITY Username and Password Reset',20,'23 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG03 - XFINITY Username and Password Reset',30,'23 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG04 - XFINITY Username and Password Reset',40,'23 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG05 - XFINITY Username and Password Reset',50,'23 Sep 2016')

    SELECT * FROM @ITGDaillyMetrics

    Now based on your requirements I'm a little confused. You want a column called date range but have the dates listed out in a list. I think this is a bad idea. Also in your example you have a variety of backend errors associated with the same 'ITG' but you sample data only has one unique error per ITG. For example ITG01 only ever has error 10 while ITG02 only has error 20. If this is not reflective of how your real data is you should fix it since people will spend time to help you, only to end up being told it's not quite working as you expected. If you only care about a date range than you only need to know about 2 dates and not a list of all dates within that range. If you are looking for errors listed out by date than that is not a good way of going about displaying that information.

    The more information we have the more we can help.

    Cheers,


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • HI Y.B,

    Please find the desired output which we looking for.

    Pavan Kumar. Pala[font="Verdana"][/font]
  • pavanpala.vja (9/29/2016)


    HI Y.B,

    Please find the desired output which we looking for.

    In your example you are disassociating your dates with the error codes. This leads me to believe that you only care about a date range for selection purposes. This means you don't need to display your dates this way as it does not help you. I know 'how' you want the output to look but I'd be doing you a disservice if I helped you do that. What I was looking for is an explanation of what you are trying to get from your data so that I might propose a better solution.

    For example It might be more useful to you to do something like this. You select your data based on your desired date range and then get a breakdown of the backend errors by ITG which occurred during that time frame. This is just a simple example of one possible way to dissect the data. My example can also be easily converted to Dynamic SQL to allow for more flexibility with errors codes. However, before I delve into that topic I'd like more descriptive feedback from you.

    Hope this helps,

    DECLARE @ITGDaillyMetrics TABLE (

    ITG varchar (max),

    Backenderror int,

    dates date )

    insert into @ITGDaillyMetrics values ('ITG01 - XFINITY Username and Password Reset',10,'26 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG02 - XFINITY Username and Password Reset',20,'26 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG03 - XFINITY Username and Password Reset',30,'26 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG04 - XFINITY Username and Password Reset',40,'26 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG05 - XFINITY Username and Password Reset',50,'26 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG01 - XFINITY Username and Password Reset',10,'25 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG02 - XFINITY Username and Password Reset',20,'25 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG03 - XFINITY Username and Password Reset',30,'25 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG04 - XFINITY Username and Password Reset',40,'25 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG05 - XFINITY Username and Password Reset',50,'25 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG01 - XFINITY Username and Password Reset',20,'24 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG02 - XFINITY Username and Password Reset',30,'24 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG03 - XFINITY Username and Password Reset',40,'24 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG04 - XFINITY Username and Password Reset',50,'24 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG05 - XFINITY Username and Password Reset',10,'24 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG01 - XFINITY Username and Password Reset',30,'23 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG02 - XFINITY Username and Password Reset',40,'23 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG03 - XFINITY Username and Password Reset',50,'23 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG04 - XFINITY Username and Password Reset',10,'23 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG05 - XFINITY Username and Password Reset',20,'23 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG01 - XFINITY Username and Password Reset',30,'22 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG02 - XFINITY Username and Password Reset',40,'22 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG03 - XFINITY Username and Password Reset',50,'22 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG04 - XFINITY Username and Password Reset',10,'22 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG05 - XFINITY Username and Password Reset',20,'22 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG01 - XFINITY Username and Password Reset',10,'21 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG02 - XFINITY Username and Password Reset',30,'21 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG03 - XFINITY Username and Password Reset',30,'21 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG04 - XFINITY Username and Password Reset',50,'21 Sep 2016')

    insert into @ITGDaillyMetrics values ('ITG05 - XFINITY Username and Password Reset',20,'21 Sep 2016')

    --SELECT * FROM @ITGDaillyMetrics

    DECLARE @fromDate DATE = '20160901'

    DECLARE @toDate DATE = '20160930'

    SELECT

    ITG,

    SUM(CASE WHEN Backenderror = 10 THEN 1 ELSE 0 END) AS 'Error 10',

    SUM(CASE WHEN Backenderror = 20 THEN 1 ELSE 0 END) AS 'Error 20',

    SUM(CASE WHEN Backenderror = 30 THEN 1 ELSE 0 END) AS 'Error 30',

    SUM(CASE WHEN Backenderror = 40 THEN 1 ELSE 0 END) AS 'Error 40',

    SUM(CASE WHEN Backenderror = 50 THEN 1 ELSE 0 END) AS 'Error 50'

    FROM @ITGDaillyMetrics

    WHERE dates >= @fromDate AND dates <= @toDate

    GROUP BY ITG


    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • http://msbimaru.blogspot.dk/2013/01/create-comma-delimited-list-from-column.html

    Please find above link with examples to implement as per your logic. Hope, this helps 🙂

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply