Multiple row data need to be convert as Coloumn

Question

Post reply

Multiple row data need to be convert as Coloumn

pavanpala.vja

Mr or Mrs. 500

Points: 577
More actions
September 28, 2016 at 6:01 am

#321705

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]

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

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

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 1

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

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

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

pavanpala.vja Mr or Mrs. 500 Points: 577 More actions · Answer 3

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]

drew.allen SSC Guru Points: 76944 More actions · Answer 4

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

Y.B. SSChampion Points: 11642 More actions · Answer 5

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

pavanpala.vja Mr or Mrs. 500 Points: 577 More actions · Answer 6

HI Y.B,

Please find the desired output which we looking for.

Pavan Kumar. Pala[font="Verdana"][/font]

Y.B. SSChampion Points: 11642 More actions · Answer 7

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 )