Hi there, I am a new member of this site and I am not very much aware of T-sql's working. My question is what if I need to get one column's data to be the heading of another column. To be very exact I have a school's database. The table I am talking about is of the results of students. The table contains Student ID, Subject ID, Total marks of the subject, Marks obtained in the subject. Now I want to print a report by generating data from this table. Right now the data is something like this StuID - - - SubID - - - -Tot - - -Obt 1 - - - - - - -1 - - - - - - -50 - - - 38 1 - - - - - - -2 - - - - - - -50 - - - 41 1 - - - - - - -3 - - - - - - -50 - - - 42 1 - - - - - - -4 - - - - - - -50 - - - 40 2 - - - - - - -1 - - - - - - -50 - - - 35 2 - - - - - - -2 - - - - - - -50 - - - 40 2 - - - - - - -3 - - - - - - -50 - - - 42 2 - - - - - - -4 - - - - - - -50 - - - 41
StudentID and SubjectID fields are related to other tables so I can get the names from there but when I need the report I need the data in the form of StuID - Sub 1 - - - Sub 2 - - - Sub 3 - - - -Sub4 1 - - - - 38 - - - - - - 41 - - - - - - 42 - - - - - - 40 2 - - - - 35 - - - - - - 40 - - - - - - 42 - - - - - - 41
The Subjects can be different for different students so the query should be dynamic instead of hard coding the names of the subjects. I hope I am clear with my question. The subjectIDs or their names will become the headings and they will contain the obtained marks for that subjects in their columns just for the reports. I have also checked the PIVOT function but was not able to do what I wanted. Thanks.
we have a table called evaluation_questions, the table has following fields
queId,Question, level, parentId
the queId is primary key(auto number),where as the field "question" will have question, heading or the subheading, the level describes the hierarchy of the field "Question", 0 means its a heading, 1 means a subheading and 2 means a question. where as the parentId means describes the immediate parentId, like if it is 1, then the parent will be English heading....
so English is a heading as level is 0 and has no parent as parentId is also 0. Reading is a subheading as level is 1 and has a prent English as its parentId=1 which is the queId oof English same is the case with writing where as recognizing words and fluency both are questions as the level is 2 and their parentId is 2 which means they come under reading.
Output:
Now What i want is to retrieve the all the questions and headings under a specified heading.like if i pass parentID as a parameter to stored procedure i should get all the headings and questions under a specified parentID.i need to fill the dataset with it.
and i need a query or Stored prodedure to get data in the below format
EMPNOABC
133250 2104 5400
Note: A, B,C ... are field name in table (SERIS) and i would like that data as the column heading.
I dont want to use below query , as we dont know the value is dyanamic
SELECT empno, SUM(CASE seris WHEN <B>'A'</B> THEN POINTS END ) AS <B> 'A' </B> SUM(CASE seris WHEN <B> 'B' </B> THEN POINTS END ) AS <B>'B'</B> FROM TABLE1 GROUP BY EMPNO
I would like to make the column heading to be the current year for the Sales I'm adding below.
SELECT dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no,dbo.arslmfil_SQL.slspsn_name, SUM(CASE WHEN year(getdate()) = qivsalesmtdytdcustsalesperson.year THEN Sales END) AS convert(varchar(4),year(getdate())) FROM dbo.QIVSalesMTDYTDCustSalesPerson INNER JOIN dbo.arslmfil_SQL ON dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no = dbo.arslmfil_SQL.humres_id GROUP BY dbo.QIVSalesMTDYTDCustSalesPerson.slspsn_no, dbo.arslmfil_SQL.slspsn_name
What I have now gives me incorrect syntax near keyword convert.
Hi All, I have never used PIVOT before but looks exactly what I want for this scenario: I have rows of dates associated with ID of Hotels and Room avalability for each Hotel/Date..... I want to show the sum of the rooms per date as columns I am using something like this:SELECT dbHotelID ,[09/20/2007]as [Today],[09/21/2007]as [Today+1],[09/22/2007]as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbRoomNumber) FOR AvailableDate IN ([09/20/2007], [09/21/2007], [09/22/2007])) AS pAs you can see I know how may days I want in advance so know how many columsn so its not dynamic.. I just dont know what the dates are:I would like to do something like: DECLARE @todayDate varchar(255), DECLARE @todayPlusOne varchar(255), DECLARE @todayPlusTwo varchar(255) SET @todayDate = CONVERT(CHAR, GETDATE(),101)SET @todayPlusOne = CONVERT(CHAR, DATEADD(d, 1, GETDATE(),101)SET @todayPlusTwo = CONVERT(CHAR, DATEADD(d, 2, GETDATE(),101) SELECT dbHotelID,@todayDate as Today,@todayPlusOne as [Today+1],@todayPlusTwo as [Today+2] FROM vwRoomAvailable PIVOT (SUM(dbHotelRoomAvailabilityNumber) FOR AvailableDate IN ([@todayDate], [@todayPlusOne], [@todayPlusTwo])) AS pBut I can’t seem to put the variable in the PIVOT value list or GETDATE() Anyone got any ideas or do I just try and do this another way and forgot PIVOT. I am using sql server 2005 express. Thanks in advance. Lee
In SSRS/RDL, How do you format a column heading to use carriage returns?
I have a couple of instance where I have a column heading that I want spread over 3 lines. For example, the column heading "= Actual Amount" I would want centered and displayed on 3 lines, as follows:
We are experiencing problems with reporting services built-in Export to Excel. Basically, the columns get wrapped in Excel, which frustrates users who need to pivot and sort(ie the point of downloading to Excel). Is there a method for removing the column wrapping? Please help
We run 2014 enterprise. I tried this with both table and matrix controls to no avail.
In the table scenario, I drag the table control over, instruct ssrs that a group name will go into column 1 and a sales figure in column2. Then I highlight the sales figure cell, add a column group on month number and generate my report off some june and july data.
ssrs understands that the months now expand horizontally but the rows alternate one with june filled in (blanks in july) and the next with july filled in for the same group name. I believe I got all my sort by conditions set but am not sure.
I tried all sorts of combos in the tablix and group properties before giving up.
i just clicked on Advanced mode in Column Group, and then in Row Group Side i set Fixed Data=true for first top static. I'm using local report not server report and i'm displaying that local report in Reportviewer. Now also its not working....
Hi to all.Many times i saw that some people wrap the single insert, delete or updatestatements to transaction.My question is suppose i have procedure , and inside this procedure i performupdate i exatly know that only one row will be updated , is necessary or wheni need to wrap it with transaction------------------------------------------------------------------------------CREATE PROCEDURE Test_sp@ID intasUPDATE MyTableSET col1 = 'bla bla'WHERE [ID] = @ID-------------------------------------------------------------------------------in code above only on row will be updated so why some times i see :------------------------------------------------------------------------------CREATE PROCEDURE Test_sp@ID intasBEGIN TRANUPDATE MyTableSET col1 = 'bla bla'WHERE [ID] = @IDSELECT@rowcount = @@ROWCOUNT,@error = @@ERRORIF @error = 0 AND @rowcount =1GOTO SUCS_STEPELSEROLLBACK TRANSACTION TRC_EVENTGOTO ERROR_STEPENDERROR_STEP:RETURN 0SUCS_STEP:COMMIT TRANSACTION TRC_EVENTRETURN 1GO-------------------------------------------------------------------------------The update statement is itself in transaction so why there are need to wrapto tranasction.So PLZ explain in wich cases i need to wrap (INSERT, UPDATE, DELETE)statements intotransactions????ONLY when i perform a single statement i.e one insert or one delete or oneupdate !!!!TNX.--"Imagination is more important than knolwege" (Albert Einshtein)Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200508/1
I have a situation where our users will key into a parameter field the name for the heading. I callled the parameter heading VarHdg1. How do I go about doing this? I tried printing @VarHdg1 but nothing came out. Please advise.
Hi I have a query which produces effectively a pivottable. Is there any way I can dynamically assign the column headings ie the code on each line after AS rather than hard coded as I have currently
Extract of Current SP
CREATE PROC dbo.FairValeSummaryPivot @BatchRunID INT AS SET NOCOUNT ON SELECT MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn1 - Tn0' , MIN(CASE WHEN Tn = '0' THEN PVBalance END) AS 'Tn0 - Tn-1' , MIN(CASE WHEN Tn = '-1' THEN PVBalance END) AS 'Tn-1 - Tn-2', MIN(CASE WHEN Tn = '-2' THEN PVBalance END) AS 'Tn-2 - Tn-3', MIN(CASE WHEN Tn = '-3' THEN PVBalance END) AS 'Tn-3 - Tn-4', MIN(CASE WHEN Tn = '-4' THEN PVBalance END) AS 'Tn-4 - Tn-5', -- and so on FROM FVSummary WHERE BatchRunID = @BatchRunID GO
what I would like would be along the lines of
MIN(CASE WHEN Tn = '1' THEN PVBalance ELSE 0 END) AS 'Tn' + Tn + ' - Tn' + Tn-1, ,
I am using visual studio reporting services to create some overviews. Several of my columns contain data which are extremely long, but it is shown as wrapped text which makes that my cells are becoming really big. Is there an option in Visual Studio to unwrap this text (like you can do in Excel)?
Is there a way to distribute merge and transactional replication commands/files such that a slight hiccup in the connection does not cause replication to fail? Maybe like wrapping them in a zip or other type of file?
A string needs to be stored in a SQL 2000 table varchar(255) fileld in such a way that when emailed or printed out, it will display as below: Name: John DoePhone: 213-444-5555Email:jdoe@test.comCity: New YorkCountry: USA How can such a string be constituted?Thanks
Hi all,I want to create a c# method that wrap a sql stored procedure and is mantained updated in c#.Ex: Stored Procedure on db:insTableA(par varchar(255)....I want to create a class in c# like this:public class insTableA { public object Execute(String par) { // call execution of stored insTableA ................ } } Using VS 2005 e Sql Server 2005you know a method to do this and to mantain (automatically!) the c# class update when I change the stored procedure signature (ex: if change the number or datatype of parameters)??? Thanks....Luca
Hi all,I'm a newbie to SQL and I need help with investigating what ways arepossible to build an interface of some sort that wraps around a SQLquery script.I have a simple SQL query which we normally run inside Query Analyzerto update certain fields in the SQL DB based on FQDN provide by theuser. The user normally opens up the query, edit the script to replaythe xxxxx in the line "set @FQDN="xxxxx" with the node name and thenfires it off.I would like to know what are the ways to wrap a simple user interfacearound it so the user does not have to open and edit the scriptdirectly. A simple GUI or Excel type form interface that has a freeform text field for input and then a button to run the script.Obviously, I would like to see the output in some sort of output paneas well.Is this something that can be done in Query Analyzer ?Thanks in advance,Michael.
We had finished converting a lot of our reports from Crystal Reports to SSRS. Upon doing so, one report, the customer's invoice has comments in the report that contain (s) at then end of a word to indicate singular or plural. Crystal reports handled the wrap correctly and kept the word together including the (s), but in SSRS, the (s) is being wrapped to the next line, which doesn't look good at all.
what i need is very simple ...i just want to show very long strings in sql reporting services report but i can't find any way to wrap up the very long text in the text box of a matrix ..i mean there is no property says word wrap or text wrap in sql reprting service..there are can grow and can shrink properties but these is not what i want....when i set it as can grow false then user has no way to read the rest of the text , there is no preview when i set false 'can grow' propety.. basically what i want is , users are able to read the rest of the text when they move the mouse over the text and can grow should be false , so column will not take any space... please help !
i am creating/uploading a new file on the webserver, and if it is successfully i want to insert a record in the database (with the filename).is there a way to create a transaction for this so that if either operation fails they both fail?
Because of a limitation on a piece of software I'm using I need to take a large varchar field and force a carriage return/linebreak in the returned sql. Allowing for a line size of approximately 50 characters, I thought the approach would be to first find the 'spaces' in the data, so as to not split the line on a real word. achieve.
--===== Simulate a passed parameter DECLARE @Parameter VARCHAR(8000) SET @Parameter = (select a_notes from dbo.notestuff as notes where a_id = '1')
I've got some SQL that works as far as returning a recordset from a series ofUNION statements.viz:SELECT whateverUNION thisUNION thatUNION otherNow I want to group and sum on it's results.Started out tying:SELECT * FROM(union stuff)....but couldn't even get past the syntax check.Where I'm headed is a sort of pivot table presentation of some hours dataassociated with various projects with a column for each of six date ranges.Bottom line: can somebody give me a pointer to the syntax needed to wrap thoseUNION statements and then select/group/sum their results?--PeteCresswell
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A B C D E F G H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".