Pivoting rows and columns is possible?
-
quarta-feira, 15 de agosto de 2012 18:17Hello everybody!
I have the following list one below which I would like to present as listed 2. However, pivoting rows and columns is possible? My example pivoting only the records, the columns are fixed (the day of the month).
Any ideas?
I hope I have been clear enough :)Doria
Todas as Respostas
-
quarta-feira, 15 de agosto de 2012 18:18User Domain Qtd
------------------------- ---------------------------------------------------------------------------------------------------- -----------
adaircampos a.rad.msn.com 697
adaircampos ad.doubleclick.net 332
adaircampos adserver.ig.com.br 285
adaircampos adsimg.ig.com 52
adaircampos bn.uol.com.br 192
adaircampos by147w.bay147.mail.live.com 385
adaircampos click.uol.com.br 187
adaircampos exch-e.atdmt.com 176
adaircampos googleads.g.doubleclick.net 56
adaircampos m.adnxs.com 190
adaircampos noticias.r7.com 128
adaircampos noticias.terra.com.br 61
adaircampos p2.trrsf.com.br 1749
adaircampos platform.twitter.com 80
adaircampos ppi.terra.com.br 1142
adaircampos r7.com 79
adaircampos rad.msn.com 709
adaircampos sn130w.snt130.mail.live.com 132
adaircampos tvg.globo.com 151
adaircampos vitrines.globo.com 147
adaircampos www.globo.com 288
adaircampos www.google.com.br 322
adaircampos www.r7.com 233
adaircampos www.terra.com.br 531
adaircampos www.tjms.jus.br 59
adaircampos www.uol.com.br 58
adaircampos www1.caixa.gov.br 63
adaircampos ---------- 9739
adalbertoescobar ad.doubleclick.net 73
adalbertoescobar ava.grupouninter.com.br 424
adalbertoescobar bn.uol.com.br 95
adalbertoescobar br.mg5.mail.yahoo.com 151
adalbertoescobar bs.serving-sys.com 75
adalbertoescobar click.uol.com.br 62
adalbertoescobar googleads.g.doubleclick.net 545
adalbertoescobar ia.nspmotion.com 63
adalbertoescobar p2.trrsf.com.br 1377
adalbertoescobar ppi.terra.com.br 648
adalbertoescobar s1.trrsf.com.br 86
adalbertoescobar saude.terra.com.br 71
adalbertoescobar selos.climatempo.com.br 320
adalbertoescobar toolbarqueries.google.com.br 1573
adalbertoescobar unico.facinter.br 91
adalbertoescobar vidaeestilo.terra.com.br 81
adalbertoescobar www.campograndenews.com.br 61
adalbertoescobar www.correiodoestado.com.br 748
and goes on...Doria
-
quarta-feira, 15 de agosto de 2012 18:18User 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 Total
-------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
alessandroazevedo 0h 0h 0h 1h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 1h 0h 0h 0h 0h 2h
alexandreseneda 0h 5h 0h 17h 1h 22h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 8h 9h 0h 0h 0h 0h 0h 0h 23h 0h 0h 0h 8h 93h
andersonsantos 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 5h 0h 0h 0h 5h
andreytamura 0h 0h 1h 4h 1h 0h 0h 0h 0h 0h 0h 2h 0h 0h 0h 0h 0h 0h 1h 0h 0h 1h 1h 0h 0h 0h 0h 0h 0h 0h 0h 11h
bob 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h
carmelinemedeiros 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h
cleberson 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 2h 0h 0h 0h 2h
danielbratficher 0h 10h 11h 9h 9h 0h 0h 0h 0h 9h 9h 10h 17h 0h 0h 14h 28h 19h 13h 10h 0h 0h 5h 9h 12h 15h 12h 0h 0h 4h 9h 234h
doria 0h 1h 0h 1h 0h 2h 0h 1h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 5h 0h 0h 0h 0h 0h 0h 2h 0h 5h 5h 0h 0h 22h
douglasbuchara 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h
emanuelsilva 0h 0h 0h 0h 0h 0h 0h 2h 0h 0h 0h 0h 0h 0h 2h 0h 0h 2h 2h 0h 0h 0h 0h 3h 9h 10h 0h 0h 2h 0h 0h 32h
fernandoroque 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 1h 0h 0h 0h 0h 1h
gustavomoia 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 1h 0h 0h 0h 1h
jeffersonsantos 0h 0h 22h 0h 21h 8h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 26h 0h 19h 10h 0h 0h 0h 26h 0h 0h 17h 4h 0h 5h 24h 182h
jezersantos 0h 2h 4h 0h 0h 0h 0h 3h 0h 2h 4h 3h 0h 0h 0h 13h 14h 26h 17h 14h 0h 12h 13h 1h 8h 0h 20h 1h 0h 0h 0h 157h
lucianogai 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 2h 2h
marcelohautequest 0h 0h 1h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 1h
maurojunior 0h 0h 0h 0h 0h 0h 2h 1h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 1h 5h 5h 1h 7h 2h 0h 3h 0h 0h 0h 0h 27h
parajaramoraes 0h 0h 0h 0h 0h 1h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 7h 4h 1h 0h 0h 0h 0h 13h
sebastiaomariano 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 0h 2h 0h 0h 0h 0h 0h 0h 4h 0h 5h 0h 0h 0h 3h 1h 0h 1h 2h 18h
Total 0h 18h 39h 32h 32h 33h 2h 7h 0h 11h 13h 15h 17h 2h 2h 27h 68h 47h 65h 44h 9h 18h 25h 46h 38h 31h 81h 19h 7h 10h 45h 803h
Warning: Null value is eliminated by an aggregate or other SET operation.Doria
-
sexta-feira, 17 de agosto de 2012 08:05Moderador
Hi Doria,
Thank you for your question.
I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
Thank you for your understanding and support.Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help. -
sexta-feira, 17 de agosto de 2012 14:32
Sure, take your time...
Doria
-
sexta-feira, 17 de agosto de 2012 15:41
Examine pivot and unpivot
http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
-
sexta-feira, 17 de agosto de 2012 19:46Moderador
Hi Doria,
Something like the following (although this is for only 7 days) assuming a table with a UserName, Qty and Day column:
SELECT UserName, [1],[2],[3],[4],[5],[6],[7]
FROM
(SELECT UserName, Qty, Day
FROM PivotTest) p
PIVOT
(
SUM (Qty)
FOR Day IN
( [1],[2],[3],[4],[5],[6],[7])
) AS pvt
ORDER BY pvt.UserName;Thanks,
Cathy Miller
-
sexta-feira, 17 de agosto de 2012 20:03
i suggest to use DYNAMIC SQL so that way we don't need to worry about number of those pivoted columns and name of those columns.Hi Doria,
Something like the following (although this is for only 7 days) assuming a table with a UserName, Qty and Day column:
SELECT UserName, [1],[2],[3],[4],[5],[6],[7]
FROM
(SELECT UserName, Qty, Day
FROM PivotTest) p
PIVOT
(
SUM (Qty)
FOR Day IN
( [1],[2],[3],[4],[5],[6],[7])
) AS pvt
ORDER BY pvt.UserName;Thanks,
Cathy Miller
ANK HIT - if reply helps, please mark it as ANSWER or helpful post
- Sugerido como Resposta ank hit sexta-feira, 17 de agosto de 2012 20:10
- Não Sugerido como Resposta Naomi NMicrosoft Community Contributor, Moderator sábado, 18 de agosto de 2012 20:34
-
sexta-feira, 17 de agosto de 2012 20:09
Something like this,
declare @col varchar(8000), @query varchar(8000); SELECT @COL = stuff((select ',' + quotename(day) FROM (select distinct day from dbo.[your table]) X ORDER BY [day] For XML Path(''),type).value('.', 'VARCHAR(max)'),1,1,'') select @QUERY = N'SELECT * FROM (SELECT UserName, Qty, [Day] FROM PivotTest) p PIVOT (max(qty) FOR [ANALYTE] IN ( '+@COL4 +')) AS pvt ORDER BY pvt.UserName'; execute(@query)
ANK HIT - if reply helps, please mark it as ANSWER or helpful post
- Sugerido como Resposta ank hit sexta-feira, 17 de agosto de 2012 20:10
- Não Sugerido como Resposta Naomi NMicrosoft Community Contributor, Moderator sábado, 18 de agosto de 2012 20:34
-
domingo, 19 de agosto de 2012 13:05
T-SQL dinamic, of course! However, I do not know how to convert a vertical list of domains in a horizontal list without using cursors; it was not clear to me why you have used the clause 'FOR XML'.
--
Below my entry table:
User Domain
-------------------- --------------------------------------------------
alexandreaugusto adclient-uol.lp.uol.com.br
alexandreaugusto bn.uol.com.br
alexandreaugusto adclient-uol.lp.uol.com.br
alexandreaugusto bn.uol.com.br
darciyumiko a.rad.msn.com
darciyumiko exch-e.atdmt.com
darciyumiko selos.climatempo.com.br
darciyumiko www.assetur.com.br
darciyumiko www.campograndenews.com.br
darciyumiko www.facebook.com
darciyumiko www.facebook.com
denisfilho platform.twitter.com
denisfilho selos.climatempo.com.br
denisfilho www.tjms.jus.br
irancoelho diversao.terra.com.br
irancoelho esportes.terra.com.br
irancoelho platform.twitter.com
irancoelho sso.terra.com.br
irancoelho www.facebook.com
(20 row(s) affected)
Doria
-
segunda-feira, 20 de agosto de 2012 02:36In time, I'm trying to create an initial example, not using all the columns at this time. However, I got an error message, as shown in the image below. Can anyone help me?
Doria
-
segunda-feira, 20 de agosto de 2012 02:37
Doria
-
segunda-feira, 20 de agosto de 2012 08:36Moderador
Try putting [br.uol.com.br] into the squared brackets, not in single quotes, e.g.
PIVOT (count(Domain) FOR [User] IN ([br.uol.com.br],[www.facebook.com])) pvt
Also remove ORDER BY pvt.[User] from the query as after pivoting you will not have [User] column.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
segunda-feira, 20 de agosto de 2012 19:27Great, thanks for your help!
--
SELECT [User], [bn.uol.com.br] AS UOL, [www.facebook.com] AS FaceBook
FROM dbo.TableValued
PIVOT
(
COUNT(Domain) FOR Domain IN ( [bn.uol.com.br], [www.facebook.com] )
) AS pvt;
GO
User UOL FaceBook
---------------------------------------------------------------------------------------------------- ----------- -----------
alexandreaugusto 2 0
darciyumiko 0 2
denisfilho 0 0
irancoelho 0 1
(5 row(s) affected)
However, I am still with two questions:
1. How can I convert multiple records (one column) into a single line (multiple columns) using SQL?
2. The PIVOT clause allows vertical and horizontal totalizers as the GROUP BY ROLLUP()?
If anyone can share, thanks in advance.Doria
-
segunda-feira, 20 de agosto de 2012 19:31Moderador
PIVOT does not allow vertical and horizontal totalizers. It is much easier to add if you're using CASE based pivot and GROUP BY, e.g.
Understanding SQL Server 2000 Pivot with Aggregates
For every expert, there is an equal and opposite expert. - Becker's Law
My blog- Marcado como Resposta Kalman TothMicrosoft Community Contributor, Moderator sexta-feira, 31 de agosto de 2012 21:16
-
segunda-feira, 20 de agosto de 2012 20:19It's a shame! Any idea about question number 1?
Doria
-
segunda-feira, 20 de agosto de 2012 20:21ModeradorPIVOT or case based pivot is what you use for #1. I thought it's already clear.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
sexta-feira, 31 de agosto de 2012 15:25
Thanks Naomi N for all your help and the help of others too!
It's really a shame that the PIVOT does not support totalizers ...
See you,
Doria
-
quarta-feira, 14 de novembro de 2012 16:47
Hi team!
May someone share if the command PIVOT allows the restriction of groups like the HAVING clause in the GROUP BY command?
Thanks, :)
Doria
-
quarta-feira, 14 de novembro de 2012 16:53
By the way, I would like to share my solution for a dynamic PIVOT:
DECLARE
@sql AS NVARCHAR(MAX),
@domain AS NVARCHAR(100),
@first AS INT;
--
DECLARE C CURSOR FAST_FORWARD FOR
SELECT DISTINCT [Domain] FROM #temp1 ORDER BY [Domain];
SET @first = 1;
SET @sql = N'SELECT * FROM (SELECT [User], [Domain], [FQDN] FROM #temp1) AS D
PIVOT(COUNT([FQDN]) FOR [Domain] IN(';
OPEN C
FETCH NEXT FROM C INTO @domain;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @first = 0
SET @sql = @sql + N','
ELSE
SET @first = 0;
SET @sql = @sql + QUOTENAME(@domain);
FETCH NEXT FROM C INTO @domain;
END
CLOSE C;
DEALLOCATE C;
SET @sql = @sql + N')) AS P;';
--PRINT @sql;
EXEC sp_executesql @stmt = @sql;
GO
Doria
-
quarta-feira, 14 de novembro de 2012 16:54ModeradorThis question is not entirely clear and it's better if you ask with a particular sample to show your case.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
sábado, 17 de novembro de 2012 22:46Hello Naomi N!
Thanks for your response! Well, what I want to check is if the PIVOT command has a way of, after pivoting, display only groups that meet a certain condition, as the HAVING clause does.
My example of dynamic PIVOT is above.
I hope I was clear enough this time, :)Doria
-
quarta-feira, 21 de novembro de 2012 00:14
Sorry Naomi N, I forgot to show you the message I'm getting when I run the above dynamic PIVOT; comes up a table with a few million records and I do not know the cause of the error message below:
--
Changed database context to 'master'.
(2944899 rows affected)
Msg 8618, Level 16, State 2, Server SRVWI004 \ SQLEXPRESS, Line 1
The query processor Could not produce a query plan because a worktable is required, and its minimum row size exceeds performer the allowable maximum of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query. If the query has a GROUP BY or ORDER BY clause, consider Reducing the number and / or size of the fields in the clause. Consider using prefix (LEFT ()) or hash (CHECKSUM ()) of fields for grouping or prefix for ordering. Note However That this will change the behavior of the query.
Therefore I questioned about a HAVING clause for the PIVOT command ...Doria
-
quarta-feira, 21 de novembro de 2012 00:54Moderador
Apparently there are too many values for the pivot columns to create. Instead of your code try
DECLARE @sql AS NVARCHAR(MAX), @domain AS NVARCHAR(max) select @domain = stuff((select ', ' + quotename(domain) from (select top (100) domain from (select distinct domain from #temp) t order by domain) t1 order by domain for XML PATH('')),1,2,'') -- columns - take only first 100 values SET @sql = N'SELECT * FROM (SELECT [User], [Domain], [FQDN] FROM #temp1) AS D PIVOT(COUNT([FQDN]) FOR [Domain] IN (' + @Domain + ')) pvt' PRINT @sql; EXEC sp_executesql @stmt = @sql; GO
What print @SQL produces?
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
quarta-feira, 21 de novembro de 2012 13:42
Thanks for your reply Naomi N! However, I do not want to limit the query to the 100 first domains, but the most accessed by users. Therefore the need for a filter after grouping ...
Doria
-
quarta-feira, 21 de novembro de 2012 13:45ModeradorIf you want the most accessed by users, simply include them in your query when you construct list of domains for PIVOT.
For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
quinta-feira, 22 de novembro de 2012 11:33
Hummm, may you share an example?
Doria
-
quinta-feira, 22 de novembro de 2012 13:33Moderador
DECLARE @sql AS NVARCHAR(MAX), @domain AS NVARCHAR(max) select @domain = stuff((select ', ' + quotename(domain) from (select top (100) domain from (select domain, count(domain) as UsageFrequency from #temp GROUP BY domain) t order by UsageFrequency DESC) t1 order by domain for XML PATH('')),1,2,'') -- columns - take only first 100 values SET @sql = N'SELECT * FROM (SELECT [User], [Domain], [FQDN] FROM #temp1) AS D PIVOT(COUNT([FQDN]) FOR [Domain] IN (' + @Domain + ')) pvt' PRINT @sql; EXEC sp_executesql @stmt = @sql; GOIn other words, instead of using 100 distinct domains sorted by name we will use 100 most frequently used domains.For every expert, there is an equal and opposite expert. - Becker's Law
My blog -
segunda-feira, 26 de novembro de 2012 12:41
Got it! :)
Doria
