September 26, 2016 at 6:04 pm
I am collecting index fragmentation report on a production server by running a job remotely using sqlps. The report is output on a txt file, which I copied over in an excel file and then imported into the database. It looks something like :
DATE DATA INFO
2016-09-16 00:00:00.000 Schema dbo
2016-09-16 00:00:00.000 Table Employer
2016-09-16 00:00:00.000 Index EmployerIdx
2016-09-16 00:00:00.000 fragmentation 43.851409052
2016-09-16 00:00:00.000 page_count 23420
2016-09-16 00:00:00.000 Schema dbo
2016-09-16 00:00:00.000 Table Endorsement
2016-09-16 00:00:00.000 Index IdxAssociationId
I wan to the data to have the following columns
Date, schema, table, index, fragmentation, page_count
and give me the right resultset. Hence, I used the pivot function like below:
select * from dbo.test
pivot
( min(info)
for data in ([schema],
, [index], [fragmentation], [page_count])
) as pivoted
However, I want to see all the data, not just min or max. Is there a way to achieve what I want?
September 26, 2016 at 8:00 pm
Something like this...
IF OBJECT_ID('tempdb..#temp', 'U') IS NOT NULL
DROP TABLE #temp;
SELECT
x.Date,
x.Type,
x.Value
INTO #temp
FROM ( VALUES
('2016-09-16 00:00:00.000', 'Schema', 'dbo'),
('2016-09-16 00:00:00.000', 'Table', 'Employer'),
('2016-09-16 00:00:00.000', 'Index', 'EmployerIdx'),
('2016-09-16 00:00:00.000', 'fragmentation', '43.851409052'),
('2016-09-16 00:00:00.000', 'page_count', '23420'),
('2016-09-16 00:00:00.000', 'Schema', 'dbo'),
('2016-09-16 00:00:00.000', 'Table', 'Endorsement'),
('2016-09-16 00:00:00.000', 'Index', 'IdxAssociationId')
) x ([Date], [Type], [Value]);
SELECT
[Schema] = MIN(CASE WHEN t.Type = 'Schema' THEN t.Value END),
[Table] = MIN(CASE WHEN t.Type = 'Table' THEN t.Value END),
[Index] = MIN(CASE WHEN t.Type = 'Index' THEN t.Value END),
[fragmentation] = MIN(CASE WHEN t.Type = 'fragmentation' THEN t.Value END),
[page_count] = MIN(CASE WHEN t.Type = 'page_count' THEN t.Value END)
FROM
#temp t;
Results...
Schema Table Index fragmentation page_count
---------------- ---------------- ---------------- ---------------- ----------------
dbo Employer EmployerIdx 43.851409052 23420
September 26, 2016 at 8:10 pm
If you want all of the data...
WITH
cte_temp AS (
SELECT
rn = ROW_NUMBER() OVER (PARTITION BY t.[Date], t.[Type] ORDER BY t.[Value]),
t.Date,
t.Type,
t.Value
FROM
#temp t
)
SELECT
[Schema] = MIN(CASE WHEN t.Type = 'Schema' THEN t.Value END),
[Table] = MIN(CASE WHEN t.Type = 'Table' THEN t.Value END),
[Index] = MIN(CASE WHEN t.Type = 'Index' THEN t.Value END),
[fragmentation] = MIN(CASE WHEN t.Type = 'fragmentation' THEN t.Value END),
[page_count] = MIN(CASE WHEN t.Type = 'page_count' THEN t.Value END)
FROM
cte_temp t
GROUP BY
t.rn
Results...
Schema Table Index fragmentation page_count
---------------- ---------------- ---------------- ---------------- ----------------
dbo Employer EmployerIdx 43.851409052 23420
dbo Endorsement IdxAssociationId NULL NULL
September 27, 2016 at 10:16 am
Hi,
Thanks for trying to help but I do not think I made the problem very clear. The data that I presented is just a small chunk of the data for 1 day. I have data ranging 30 days. I ran the query you guys suggested, but it came back with similar results as mine.
September 27, 2016 at 10:49 am
September 27, 2016 at 11:42 am
Hi Jason,
The raw data excel has the actual data as it is now.
The desiredformat.xlxs shows how I want to capture the data although the data is not actually real. However, I hope you understand what I am trying to capture.
Thanks,
Sanjay
September 27, 2016 at 12:45 pm
Your problem is that you have nothing to bind the row groups together. Take a look at what I did below by adding a "SetNum" column to the data...
Once that was added it's just a simple cross tab...
IF OBJECT_ID('tempdb..#RawData', 'U') IS NOT NULL
DROP TABLE #RawData;
SELECT
x.SetNum,
x.Date,
x.Data,
x.Info
INTO #RawData
FROM (VALUES
(1, '2016-09-16', 'Schema', 'dbo'),
(1, '2016-09-16', 'Table', 'Employer'),
(1, '2016-09-16', 'Index', 'Employer'),
(1, '2016-09-16', 'avg_fragmentation_in_percent', '43.8514'),
(1, '2016-09-16', 'page_count', '23420'),
(2, '2016-09-16', 'Schema', 'dbo'),
(2, '2016-09-16', 'Table', 'PolicyEn'),
(2, '2016-09-16', 'Index', 'IdxAssoc'),
(2, '2016-09-16', 'avg_fragmentation_in_percent', '25.28649'),
(2, '2016-09-16', 'page_count', '187353'),
(3, '2016-09-16', 'Schema', 'dbo'),
(3, '2016-09-16', 'Table', 'Policy'),
(3, '2016-09-16', 'Index', 'PolicyId'),
(3, '2016-09-16', 'avg_fragmentation_in_percent', '10.38382'),
(3, '2016-09-16', 'page_count', '46245'),
(4, '2016-09-16', 'Schema', 'dbo'),
(4, '2016-09-16', 'Table', 'Policy'),
(4, '2016-09-16', 'Index', 'PolicyId'),
(4, '2016-09-16', 'avg_fragmentation_in_percent', '8.421022'),
(4, '2016-09-16', 'page_count', '69279'),
(5, '2016-09-16', 'Schema', 'dbo'),
(5, '2016-09-16', 'Table', 'AcePolic'),
(5, '2016-09-16', 'Index', 'AcePolic'),
(5, '2016-09-16', 'avg_fragmentation_in_percent', '5.67119'),
(5, '2016-09-16', 'page_count', '3275767'),
(6, '2016-09-17', 'Schema', 'dbo'),
(6, '2016-09-17', 'Table', 'Employer'),
(6, '2016-09-17', 'Index', 'Employer'),
(6, '2016-09-17', 'avg_fragmentation_in_percent', '11.89065'),
(6, '2016-09-17', 'page_count', '21437'),
(7, '2016-09-18', 'Schema', 'dbo'),
(7, '2016-09-18', 'Table', 'Employer'),
(7, '2016-09-18', 'Index', 'Employer'),
(7, '2016-09-18', 'avg_fragmentation_in_percent', '12.28764'),
(7, '2016-09-18', 'page_count', '21485'),
(8, '2016-09-18', 'Schema', 'dbo'),
(8, '2016-09-18', 'Table', 'Policy'),
(8, '2016-09-18', 'Index', 'PolicyId'),
(8, '2016-09-18', 'avg_fragmentation_in_percent', '5.479785'),
(8, '2016-09-18', 'page_count', '45239'),
(9, '2016-09-19', 'Schema', 'dbo'),
(9, '2016-09-19', 'Table', 'Employer'),
(9, '2016-09-19', 'Index', 'Employer'),
(9, '2016-09-19', 'avg_fragmentation_in_percent', '12.28764'),
(9, '2016-09-19', 'page_count', '21485'),
(10, '2016-09-19', 'Schema', 'dbo'),
(10, '2016-09-19', 'Table', 'Policy'),
(10, '2016-09-19', 'Index', 'PolicyId'),
(10, '2016-09-19', 'avg_fragmentation_in_percent', '5.518232'),
(10, '2016-09-19', 'page_count', '45250'),
(11, '2016-09-20', 'Schema', 'dbo'),
(11, '2016-09-20', 'Table', 'Employer'),
(11, '2016-09-20', 'Index', 'Employer'),
(11, '2016-09-20', 'avg_fragmentation_in_percent', '28.93375'),
(11, '2016-09-20', 'page_count', '22040'),
(12, '2016-09-20', 'Schema', 'dbo'),
(12, '2016-09-20', 'Table', 'PolicyEn'),
(12, '2016-09-20', 'Index', 'IdxAssoc'),
(12, '2016-09-20', 'avg_fragmentation_in_percent', '12.55799'),
(12, '2016-09-20', 'page_count', '174144'),
(13, '2016-09-20', 'Schema', 'dbo'),
(13, '2016-09-20', 'Table', 'Policy'),
(13, '2016-09-20', 'Index', 'PolicyId'),
(13, '2016-09-20', 'avg_fragmentation_in_percent', '7.702531'),
(13, '2016-09-20', 'page_count', '45894'),
(14, '2016-09-20', 'Schema', 'dbo'),
(14, '2016-09-20', 'Table', 'Policy'),
(14, '2016-09-20', 'Index', 'PolicyId'),
(14, '2016-09-20', 'avg_fragmentation_in_percent', '6.229183'),
(14, '2016-09-20', 'page_count', '68757'),
(15, '2016-09-21', 'Schema', 'dbo'),
(15, '2016-09-21', 'Table', 'Employer'),
(15, '2016-09-21', 'Index', 'Employer'),
(15, '2016-09-21', 'avg_fragmentation_in_percent', '40.50762'),
(15, '2016-09-21', 'page_count', '22418'),
(16, '2016-09-21', 'Schema', 'dbo'),
(16, '2016-09-21', 'Table', 'PolicyEn'),
(16, '2016-09-21', 'Index', 'IdxAssoc'),
(16, '2016-09-21', 'avg_fragmentation_in_percent', '17.22258'),
(16, '2016-09-21', 'page_count', '178922'),
(17, '2016-09-21', 'Schema', 'dbo'),
(17, '2016-09-21', 'Table', 'Policy'),
(17, '2016-09-21', 'Index', 'PolicyId'),
(17, '2016-09-21', 'avg_fragmentation_in_percent', '8.899991'),
(17, '2016-09-21', 'page_count', '46236'),
(18, '2016-09-21', 'Schema', 'dbo'),
(18, '2016-09-21', 'Table', 'Policy'),
(18, '2016-09-21', 'Index', 'PolicyId'),
(18, '2016-09-21', 'avg_fragmentation_in_percent', '7.197374'),
(18, '2016-09-21', 'page_count', '69178'),
(19, '2016-09-22', 'Schema', 'dbo'),
(19, '2016-09-22', 'Table', 'Employer'),
(19, '2016-09-22', 'Index', 'Employer'),
(19, '2016-09-22', 'avg_fragmentation_in_percent', '41.98051'),
(19, '2016-09-22', 'page_count', '22782'),
(20, '2016-09-22', 'Schema', 'dbo'),
(20, '2016-09-22', 'Table', 'PolicyEn'),
(20, '2016-09-22', 'Index', 'IdxAssoc'),
(20, '2016-09-22', 'avg_fragmentation_in_percent', '21.94896'),
(20, '2016-09-22', 'page_count', '184200'),
(21, '2016-09-22', 'Schema', 'dbo'),
(21, '2016-09-22', 'Table', 'Policy'),
(21, '2016-09-22', 'Index', 'PolicyId'),
(21, '2016-09-22', 'avg_fragmentation_in_percent', '9.98088'),
(21, '2016-09-22', 'page_count', '46549'),
(22, '2016-09-22', 'Schema', 'dbo'),
(22, '2016-09-22', 'Table', 'Policy'),
(22, '2016-09-22', 'Index', 'PolicyId'),
(22, '2016-09-22', 'avg_fragmentation_in_percent', '8.086702'),
(22, '2016-09-22', 'page_count', '69571')
) x ([SetNum], [Date], [Data], [Info]);
-- Solution --
SELECT
rd.SetNum,
[Date] = MIN(rd.[Date]),
[Schema] = MIN(CASE WHEN rd.Data = 'Schema' THEN rd.Info END),
[Table] = MIN(CASE WHEN rd.Data = 'Table' THEN rd.Info END),
[Index] = MIN(CASE WHEN rd.Data = 'Index' THEN rd.Info END),
[avg_fragmentation_in_percent] = MIN(CASE WHEN rd.Data = 'avg_fragmentation_in_percent' THEN rd.Info END),
[page_count] = MIN(CASE WHEN rd.Data = 'page_count' THEN rd.Info END)
FROM
#RawData rd
GROUP BY
rd.SetNum
results...
SetNum Date Schema Table Index avg_fragmentation_in_percent page_count
----------- ---------- -------- -------- -------- ---------------------------- ----------
1 2016-09-16 dbo Employer Employer 43.8514 23420
2 2016-09-16 dbo PolicyEn IdxAssoc 25.28649 187353
3 2016-09-16 dbo Policy PolicyId 10.38382 46245
4 2016-09-16 dbo Policy PolicyId 8.421022 69279
5 2016-09-16 dbo AcePolic AcePolic 5.67119 3275767
6 2016-09-17 dbo Employer Employer 11.89065 21437
7 2016-09-18 dbo Employer Employer 12.28764 21485
8 2016-09-18 dbo Policy PolicyId 5.479785 45239
9 2016-09-19 dbo Employer Employer 12.28764 21485
10 2016-09-19 dbo Policy PolicyId 5.518232 45250
11 2016-09-20 dbo Employer Employer 28.93375 22040
12 2016-09-20 dbo PolicyEn IdxAssoc 12.55799 174144
13 2016-09-20 dbo Policy PolicyId 7.702531 45894
14 2016-09-20 dbo Policy PolicyId 6.229183 68757
15 2016-09-21 dbo Employer Employer 40.50762 22418
16 2016-09-21 dbo PolicyEn IdxAssoc 17.22258 178922
17 2016-09-21 dbo Policy PolicyId 8.899991 46236
18 2016-09-21 dbo Policy PolicyId 7.197374 69178
19 2016-09-22 dbo Employer Employer 41.98051 22782
20 2016-09-22 dbo PolicyEn IdxAssoc 21.94896 184200
21 2016-09-22 dbo Policy PolicyId 9.98088 46549
22 2016-09-22 dbo Policy PolicyId 8.086702 69571
September 27, 2016 at 12:50 pm
Yes, exactly. I couldn't think of a way to bind them together. I mean the method you proposed certainly works but it's that manual work of typing the value for each and every column I was trying to avoid somehow.
Only if I could somehow group them together
September 27, 2016 at 1:11 pm
Srl832 (9/27/2016)
Yes, exactly. I couldn't think of a way to bind them together. I mean the method you proposed certainly works but it's that manual work of typing the value for each and every column I was trying to avoid somehow.Only if I could somehow group them together
The easiest fix would be to start capturing DATETIME rather than just date... Assuming of course that these values are being captured at different intervals throughout the day... Then it's a simple matter to group on the datetime column.
Of course that does nothing for the existing data... I don't know how much data you already have loaded or how important it is that you keep it but in it's current state, it's useless... I'd suggest reloading it from the .txt files or simply scrub it and move forward the correct data type and/or group number.
HTH,
Jason
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply