How to get this table to produce this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get this table to produce this?

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-18 : 11:08:41


S09	S11	S11	V01	S67	S88	S53	S10	S92
3	2	2	1	2	2	2	1	3

here's the table info:


CREATE TABLE [dbo].[Pending](
	[DOC] [varchar](4) NULL,
	
) ON [PRIMARY]

insert into Pending
select 'S09' union all
select 'S09' union all
select 'S09' union all
select 'S11' union all
select 'S11' union all
select 'V01' union all
select 'S67' union all
select 'S67' union all
select 'S88' union all
select 'S88' union all
select 'S53' union all
select 'S53' union all
select 'S10' union all
select 'S92' union all
select 'S92' union all
select 'S92'

Is it best to have the counts go down to the left since I do have more Doc's to add?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 11:14:38

have a look at PIVOT operator
http://www.tsqltutorials.com/pivot.php

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-18 : 12:02:57

Great website. Going to try it will be back if I get stuck

Thanks!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-18 : 14:09:06

Okay I tried this

SELECT Doc
FROM pending
PIVOT
(
SUM(doc) FOR [doc] IN ([TotalPending])
)
AS p

I'm getting incorrect syntax near '(' (the bold section) what am I missinig?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-18 : 14:35:52

I tried this and still getting incorrect syntax near '('

SELECT Doc
FROM pending
PIVOT
(
SUM(doc) FOR doc IN ([S09] ,[S11], [V01], [S67], [S88], [S53], [S10], [S92] )
)
AS p

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-18 : 14:57:03

Okay I'm getting there

Here's new table info:


CREATE TABLE [dbo].[Pending](
	[DOC] [varchar](4) NULL,
	[FO] varchar(4) null
) ON [PRIMARY]

insert into Pending
select 'S09', '001' union all
select 'S09', '001' union all
select 'S09', '002' union all
select 'S11', '019' union all
select 'S11', '019' union all
select 'V01', '019' union all
select 'S67', '452' union all
select 'S67', '598' union all
select 'S88', '598' union all
select 'S88', '259' union all
select 'S53', '003' union all
select 'S53', '003' union all
select 'S10', '003' union all
select 'S92', '210' union all
select 'S92', '210' union all
select 'S92', '113'

Data is not matching up when I do this:

select FO, 
count(case [doc] when 0 then 1 else 0 end) as [S09],
count(case [doc] when 1 then 1 else 0 end) as [S11]
from pending 
group by FO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-19 : 04:18:41

it should be below


SELECT *
FROM (SELECT [DOC],COUNT([FO]) AS cnt FROM pending GROUP BY [DOC])t
PIVOT
(
SUM(Cnt) FOR doc IN ([S09] ,[S11], [V01], [S67], [S88], [S53], [S10], [S92] )
)
AS p


output
---------------------------------
S09	S11	V01	S67	S88	S53	S10	S92
3	2	1	2	2	2	1	3

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-19 : 11:13:47

Pefect as usual. Please explain. How could I tweak it so the FO's go down on the left hand side?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 00:26:53

sorry didnt get that. hos whould you want FOs to come? can you please show expected output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-20 : 09:18:42

With the new table info I added the FO column. So I wanted the FO's to go down the side as such:




FO      S09        S11           V01           S67         S88           S53           S10           S92
001      2          0                0         0             0               0                0                0
002      1          0                0         0             0               0                0                0
003      0          0                0         0             0               2                1                0
019      0          2                1         0             0               0                0                0
113      0          0                0         0             0               0                0                1
210      0          0                0         0             0               0                0                2
259      0          0                0         0             1               0                0                0
452      0          0                0         1             0               0                0                0
598      0          0            0             1             8               0                0                0

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-20 : 09:22:40

[code]SELECT *
FROM (SELECT [DOC],[FO],COUNT(*) AS cnt FROM pending GROUP BY [DOC])t
PIVOT
(
SUM(Cnt) FOR doc IN ([S09] ,[S11], [V01], [S67], [S88], [S53], [S10], [S92] )
)
AS p

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-20 : 10:45:16

Thanks I tried that but got this error message:

Column 'pending.FO' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 00:43:03

[code]
SELECT *
FROM (SELECT [DOC],[FO],COUNT(*) AS cnt FROM pending GROUP BY [DOC]],[FO])t
PIVOT
(
SUM(Cnt) FOR doc IN ([S09] ,[S11], [V01], [S67], [S88], [S53], [S10], [S92] )
)
AS p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-21 : 09:04:50

Thanks so much. Could you please explain this line to me;

SELECT *
FROM (SELECT [DOC],[FO],COUNT(*) AS cnt FROM pending GROUP BY [DOC]],[FO])t
PIVOT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 09:12:56

its a derived table where i've already group by [DOC],[FO] combination and take count of each. these count figures will be pivotted later

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-10-21 : 09:43:45

Thanks again!

I'm trying to put 0 in where there are null's. I tried this but it does nothing:

SELECT *
FROM (SELECT [DOC],[FO],Count(isnull(*,0)) AS cnt FROM pending GROUP BY [DOC],[FO])t
PIVOT
(
SUM(Cnt) FOR doc IN ([S09] ,[S11], [V01], [S67], [S88], [S53], [S10], [S92] )
)
AS p

Error I'm getting is:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 09:52:52

for avoiding nulls make it like



SELECT [DOC],[FO],
ISNULL([S09],0)  AS [S09],
ISNULL([S11],0)  AS [S11],
ISNULL([V01],0)  AS [V01],
ISNULL([S67],0)  AS [S67],
ISNULL([S88],0)  AS [S88],
ISNULL([S53],0)  AS [S53],
ISNULL([S10],0)  AS [S10],
ISNULL([S92],0)  AS [S92]
FROM (SELECT [DOC],[FO],Count(*) AS cnt FROM pending GROUP BY [DOC],[FO])t
PIVOT
(
SUM(Cnt) FOR doc IN ([S09] ,[S11], [V01], [S67], [S88], [S53], [S10], [S92] )
)
AS p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBAPBFL
Starting Member

11 Posts

Posted - 2011-10-21 : 09:58:43

thanks for tutorial visakh16.

JJ297, seems like getting nulls to zeros take some dynamic sql. I would think its easier if you app can do the null to zero conversion.

check out this blog:
http://www.sqlservercentral.com/Forums/Topic853121-146-1.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 10:01:33

quote:
Originally posted by DBAPBFL

thanks for tutorial visakh16.

JJ297, seems like getting nulls to zeros take some dynamic sql. I would think its easier if you app can do the null to zero conversion.

check out this blog:
http://www.sqlservercentral.com/Forums/Topic853121-146-1.aspx

nope. why do you think its dynamic? i've not applied any dynamic sql above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBAPBFL
Starting Member

11 Posts

Posted - 2011-10-21 : 10:05:01

dynamic if you dont want to had code the DOC values

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-21 : 10:30:16

quote:
Originally posted by DBAPBFL

dynamic if you dont want to had code the DOC values

thats not just for null value conversion alone. its actually for pivot that you're applying dynamic sql.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources