September 28, 2016 at 6:01 am
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.
September 28, 2016 at 6:52 am
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
September 28, 2016 at 7:02 am
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
September 28, 2016 at 7:52 am
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)
September 28, 2016 at 8:39 am
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
September 28, 2016 at 9:52 am
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,
September 29, 2016 at 1:59 am
HI Y.B,
Please find the desired output which we looking for.
September 29, 2016 at 8:49 am
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
October 5, 2016 at 6:14 am
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