Incorrect Syntax near S.. in asp .net c#

Question

1.00/5 (1 vote)

See more:

Dear Frnds,

I have incorrect syntax near s and Max and 10 in last 3 lines.. what is the problem anyone tell me pls..

SQL

select 10prdp_totalcount as [10prdp], 
  Rate_totalamount as [Rate],
  D-Clamp_totalcount as [D-Clamp], 
  Rate_totalamount as [Rate]
from
(
  select DPNo,
    ItemName +'_'+col,  
    MatQty 
  from
  (
    select DPNo, ItemName,     
      cast(Rate as numeric(10, 2)) rate
    from  Vw_DailyEntry
  ) src
  unpivot
  (
    MatQty
    for col in (rate)
  ) unpiv
) s
pivot
(
  Max(MatQty)
  for ItemName in (10prdp_totalcount, Rate_totalamount,
              Clamp_totalcount, Rate_totalamount)
) piv
order by ItemName

Posted 6-Oct-15 2:15am

Vivek.anand34

Updated 6-Oct-15 2:27am

Naveen.Sanagasetti

v2

Add a Solution

Comments

Vivek.anand34 6-Oct-15 8:16am

Actually i reffered this query from this site: please verify it..
http://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Accepted Answer · 2015-10-06T04:57:00

Ok - solution to second problem:

First run this:

SQL

select DPNo,
ItemName +'_'+col col,
MatQty
from(
    select
        DPNo,
        ItemName,
        cast(MatQty as numeric(10, 2)) mtqty,
        cast(Rate as numeric(10, 2)) rate
    from
        Vw_DailyEntry
) src
unpivot(
    MatQty
    for col in (rate,mtqty)
) unpiv

Notice the names in [col] - These are the column names available in the pivot.

Some of these names start with a digit. That's ok - bracket them:

for col in ([10prdp_rate], [10prdp_mtqty], [10p_rate], [10p_mtqty], [D-Clamp_rate], [D-Clamp_mtqty], [D_C_rate], [D_C_mtqty])

Update these and the select columns to get some values

PS: this was my test data:

declare @Vw_DailyEntry table (
	DPNo int identity(1,1) primary key not null,
	ItemName nvarchar(max) not null,
	MatQty numeric(10, 2) not null,
	Rate numeric(10, 2) not null)

INSERT INTO @Vw_DailyEntry(
	ItemName,
	MatQty,
	Rate
)
values
('10prdp',1.0,1.0),
('10prdp',2.1,2.1),
('10prdp',3.2,3.2),
('D-Clamp',4.3,4.3),
('10p',5.4,5.4),
('D_C',6.5,6.5),
('10prdp',7.6,7.6),
('D_C',8.7,8.7),
('D_C',9.8,9.8),
('D-Clamp',10.9,10.9),
('10prdp',11.10,11.10),
('10p',12.11,12.11),
('10p',13.12,13.12),
('D_C',14.13,14.13),
('D-Clamp',15.14,15.14),
('D_C',16.15,16.15),
('10p',17.16,17.16),
('D_C',18.17,18.17),
('10p',19.18,19.18),
('D_C',20.19,20.19)

Andy Lanng · Accepted Answer · 2015-10-06T02:52:00

Solution 1

So many issues:

1: column names can not start with a digit: "10prdp_totalcount" = "_10prdp_totalcount"
1a: unless bracketed: "[10prdp_totalcount]"
2: you must name every column in anon table types: "ItemName +'_'+col = ItemName +'_'+col as ItemnameCol,"
3: column names cannot contain operands: "D-Clamp_totalcount" = "D_Clamp_totalcount"
3a: unless bracketed: "[D-Clamp_totalcount]"
4: pivit column names must be unique: 2x"Rate_totalamount"
5: order by must be a calculated column of the same scope: "order by ItemName" = "order by [D-Clamp]"

get those sorted, then see where you are ^_^

Andy

Posted 6-Oct-15 2:52am

Andy Lanng

Updated 6-Oct-15 2:53am

Comments

Vivek.anand34 6-Oct-15 9:32am

Thanks for the Reply...

I cannot understood 2nd point... i copy and paste that line it makes an error

Andy Lanng 6-Oct-15 9:38am

you are using an anonymous table. That's when you do from(select a,b from c) as d. The query will present columns a & b as part of anon table d. The issue is that if you have the column a+b then the query doesn't know what to call it. You have to give it an alias, i.e. a+b as [a+b] . This is only true for anon tables. if you have a named query like select a,b,a+b from c as d then the columns name will be a, b & unknown. But this is a named query so it's the final step and the query doesn't care

Vivek.anand34 6-Oct-15 10:06am

I have executed but I got null values:

select
_10prdp as [10prdp],
_10p_Rate as [Rate],
D_Clamp as [D-Clamp],
D_CRate as [Rate]
from
(
select DPNo,
ItemName +'_'+col col,
MatQty
from
(
select DPNo, ItemName,
cast(MatQty as numeric(10, 2)) mtqty,
cast(Rate as numeric(10, 2)) rate
from Vw_DailyEntry
) src
unpivot
(
MatQty
for col in (rate,mtqty)
) unpiv
) s
pivot
(
Max(MatQty)
for col in (_10prdp, _10p_Rate,
D_Clamp, D_CRate)
) piv

Andy Lanng 6-Oct-15 10:18am

Ok - that's a new issue. Please accept the solution here and ask another question.

In the new question, include the query you now have but also include some test data. I have no idea what Vw_DailyEntry looks like so the help I can offer is limited.

You don't have to include live data, just enough so that we can recreate what you see when you run the query.

I'll look out for the new question and you can post a link to it here.

Thanks ^_^
Andy

Vivek.anand34 6-Oct-15 10:30am

20 values are entered in that table..

but all values shown Null executing this.

Andy Lanng 6-Oct-15 10:35am

Ok - I can work with that. Post the new question and I'll get started on investigating

Vivek.anand34 6-Oct-15 10:41am

Ok. Thank you so much..

Vivek.anand34 6-Oct-15 10:46am

I have one doubt in final lines: for col in (_10prdp, _10p_Rate,
D_Clamp, D_CRate)

in this line its a db table values huh...

Andy Lanng 6-Oct-15 10:47am

yup - I'm focusing on that right now. Start a new post so I can post my code. Go, go, go :P

Vivek.anand34 6-Oct-15 10:47am

db values I have like this: 10prdp, D-Clamp,

Andy Lanng 6-Oct-15 10:48am

not with ItemName +'_'+col they aren't :/

Vivek.anand34 6-Oct-15 11:00am

Then What i put there for ItemName

Andy Lanng 6-Oct-15 11:01am

see solution 2

Vivek.anand34 6-Oct-15 11:22am

Thanks. but y i put two times in this line:
for col in ([10prdp_rate], [10prdp_mtqty], [10p_rate], [10p_mtqty], [D-Clamp_rate], [D-Clamp_mtqty], [D_C_rate], [D_C_mtqty])

Andy Lanng 6-Oct-15 11:27am

Sorry, I don't understand the question.
Your unpivot advances two catagories: rate and mtqty. Each ItemName in the pivot then has an ItemName_category, or 10p_rate & 10p_mtqty.