January 16, 2008 at 8:26 am
here is my problem:
i have just one table, named "collector" and the columns are:
ledno(nvarchar(50))' the item number of a kind of led(a mini light source) which we use
ledname(nvarchar(50))' the name of the leds
quantity(int) ' how many leds we use on that part
partname(nvarchar(50) 'the parts name
and i want it to be this way;
ledno ledname partname(part name goes dynamically according to our records(the header))
202.a led-a 3 '(this is the total led-a used in the correct partname)
205.b led-b 6'(this is the total led-b used in the correct partname)
and the "partname" header musn't repeat itself , i hope i am able to tell my problem, i know it's simple for you but i couldn't manage may self, thanks again.
January 16, 2008 at 8:30 am
Have a look at http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx
Regards,
Andras
January 16, 2008 at 8:43 am
thanks for your answer it's nice but i am suspecting that i am an idiot, becuse i couldn't relate this into my problem i just have one table and my partname header is not fixed it's count depends on my records on the table :(:crying:
January 16, 2008 at 8:48 am
oh you were andras himself sorry, i thought you're advicing me to a link that you find useful ,but youre himself may be can help me my situation is not complex but my brain stopped at this .
January 16, 2008 at 9:09 am
OK, a first draft (may need some adjustment, but I do not have your data 🙂
DECLARE @cols NVARCHAR(2000)
SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
'],[' + t2.partname
FROM collector AS t2
ORDER BY '],[' + t2.partname
FOR XML PATH('')
), 1, 2, '') + ']'
DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT ledno,
ledname, '+
@cols +'
FROM ( SELECT t1.ledno, t1.ledname, t1.quantity, t1.partname
FROM collector AS t1
) p PIVOT ( SUM(quantity) FOR partname IN ( '+
@cols +' )
) AS pvt
ORDER BY ledno;'
EXECUTE(@query)
Regards,
Andras
January 16, 2008 at 9:16 am
it' so nice to answer back to me :), i will try and work on your first draft ,then turn back to you cause i am not so fast as you 🙂 thank you very very much.
January 16, 2008 at 9:27 am
hello again i am disturbing sorry but i have the errors but i couldn't find the reason, my table structure is exactly as i mentioned before .
errors:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '&'.
Msg 1038, Level 15, State 4, Line 2
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near 'p'.
Msg 1038, Level 15, State 4, Line 31
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
January 16, 2008 at 9:37 am
Unless I'm mis-reading it - there's a [ missing on this line:
) p PIVOT ( SUM(quantity) FOR partname IN ( '+
should be
) p PIVOT ( SUM(quantity) FOR partname IN ( ['+
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 16, 2008 at 10:39 am
sorry still the same--- i feel like those asking everything guys.:(
January 16, 2008 at 10:49 am
Try this -
Instead of the EXECUTE - put in PRINT @SQL. It will output the SQL code it generates (meaning - the query it's trying to execute. Paste that into a new window in SSMS, and see where it tells you the error is, or see if you see the error.
If you don't - then paste the output over here, and someone will hopefully spot the error.
Edit: Do you have any rows with NO partname on it? because that would make this bomb...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 16, 2008 at 11:02 am
yes , i think i have (and will) have null partnames , anyway i appreciate and thanks for all helpers very much 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply