|
In MS SQL 2008 I'm often doing big SELECT * statements from a couple tables looking for data in various fields, and have to scroll sideways in the SSMS window.
What I'd like to do is for every field in whatever table I'm selecting, to pivot so its name goes into FieldName, and its value into FieldValue. Then I can use sorts and quickly find what I'm looking for.
I've seen some articles on PIVOTs but they are almost always hardcoded for a specific table, and use aggregates. I want something dynamic and simple.
ie: SELECT * FROM AnyTable
Column 1, Column 2, Column 3
Data 1, Data 2, Data 3
Into:
Column 1, Data 1
Column 2, Data 2
Column 3, Data 3
I'm guessing some stored procedure is necessary but I haven't been able to find any. Does anyone have something simple in the toolbox?
|
|
|
|
|
Ahh now I have to explain to my collegues why I am sitting here chuckling - quick, generic and pivot all in the same sentence.
There is nothing quick or generic about pivot tables. I have an article[^] that uses dynamic sql to avoid the hard coding but it is not quick and generic.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
for example two columns of table1 , have relation to the id column of table2
for example:
tbl_city: (id , title)
tbl_person: (id , name , CurrentCityID , LastCityID , ...)
now , I want to make two relations from tbl_person to tbl_city
CurrentCityID and LastCityID must relation to tbl_city
this query is wrong:
SELECT tbl_person.name , tbl_city.title , tbl_city.title
FROM tbl_person
JOIN tbl_city ON tbl_person.CurrentCityID = tbl_city.id
JOIN tbl_city ON tbl_person.LastCityID = tbl_city.id
can you help me ?
H.R
|
|
|
|
|
Yes, but you may want an AND instead.
|
|
|
|
|
Maybe you ar looking for aliases in sql queries?
I think this query is the right one for you:
SELECT
person.name as personName, currentCity.title as currentCityTitle, lastCity.title as lastCityTitle
FROM tbl_person as person
JOIN tbl_city as currentCityON person.CurrentCityID = currentCity.id
JOIN tbl_city as lastCity ON person.LastCityID = lastCity.id
Note the as keyword used in the querry.
I have no smart signature yet...
|
|
|
|
|
Stanciu Vlad wrote: Note the as keyword used in the querry.
I've always left the as out.
|
|
|
|
|
That is a common practice, but in the previous example I assumed the questioner has no or little knoledge about aliases, therefore I followed a more explicit approach. 
I have no smart signature yet...
|
|
|
|
|
Hi Friend,
I have one question about database management...
I have one Procedure which take 30 min to execute....
I have crate front end for that Process, the scenario is when user press the button to execute that Procedure but find no response and close the browser what happen for that procedure after this, i mean that procedure is executed but did't stop....that procedure is using server resource but unmanaged how to managed that.
Thanks and regard's in advance.
Sasmi
|
|
|
|
|
In case of SQL server 2008, the stored procedure won't execute if the calling program is stopped. Which database or you speaking of?
|
|
|
|
|
i am talking about sql Server 2005...
|
|
|
|
|
I believe it should also behave the same way. You can try to replicate the scenario using following in the stored procedure:
waitfor delay '00:00:10'
insert into SomeTable values ('some value')
This will cause a delay of 10 seconds before the insert. While SQL is waiting for the delay to get over, you can stop the calling application of yours and then check if the insert happened.
BTW is 30 min execution time expected or not? If not, you can check the execution plan and it would give you an idea why so much time is taken.
|
|
|
|
|
First and foremost, what user is going to sit there and wait for 30 minutes for the query to execute? I would spend my time optimising the query rather than worry about what happens WHEN the user closes the browser.
Anyone who has a stored proc taking that amount of time to execute while a user sits waiting deserves any and all problems which they get from it.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi, thanks for looking
I need to sort the results of my query, lets say i have the following values in my Articles table
Id, Title
--------------------------
1, "The less you talk..."
2, The long and...
3, -ABC of Math
4, *Something else
The result should look as follows:
3, -ABC of Math
4, *Something else
1, "The less you talk..."
2, The long and...
Notice that the title may o may not have or may have more than 1 special character at the beggining
Thanks in advance for any help
Alexei Rodriguez
|
|
|
|
|
Ok, I did this:
DECLARE @Table AS Table(
ID Int,
[Name] nVarChar(50))
INSERT @Table(ID, Name)
VALUES(1, '"The less you talk..."')
INSERT INTO @Table
SELECT 2, 'The long and...'
INSERT INTO @Table
SELECT 3, '-ABC of Math'
INSERT INTO @Table
SELECT 4, '*Something Else'
SELECT * FROM @Table
ORDER BY (SELECT CASE WHEN (UNICODE(SUBSTRING(Name,1,1))< 123)
THEN UNICODE(SUBSTRING(Name, 2,1))
ELSE Name End) ASC
Result:
ID Name
-----------------------------
3 -ABC of Math
4 *Something Else
1 "The less you talk..."
2 The long and...
The results match what you specified, but what if there is more than one 'special' character at the beginning of the string?
EDIT:
If you add more data you will find the following:
DECLARE @Table AS Table(
ID Int,
[Name] nVarChar(50))
INSERT @Table(ID, Name)
VALUES(1, '"The less you talk..."')
INSERT INTO @Table
SELECT 2, 'The long and...'
INSERT INTO @Table
SELECT 3, '-ABC of Math'
INSERT INTO @Table
SELECT 4, '*Something Else'
INSERT INTO @Table
SELECT 4, '? A Question'
INSERT INTO @Table
SELECT 4, '#Regions'
INSERT INTO @Table
SELECT 4, 'Sometimes you are right.'
INSERT INTO @Table
SELECT 4, 'Little creatures'
SELECT * FROM @Table
ORDER BY (SELECT CASE WHEN (UNICODE(SUBSTRING(Name,1,1))< 123)
THEN UNICODE(SUBSTRING(Name, 2,1))
ELSE Name End
) ASC
Result:
ID Name
-------------------------------
4 ? A Question
3 -ABC of Math
4 #Regions
4 *Something Else
1 "The less you talk..."
2 The long and...
4 Little creatures
4 Sometimes you are right.
This may not be ideal...
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
modified on Saturday, April 17, 2010 12:18 AM
|
|
|
|
|
Thanks for the reply
Its a good start, but it looks like you are only taking into account the first character and in case it is a pecial symbol you take the second, it wont work if you have values like these:
DECLARE @Table AS Table( ID Int, [Name] nVarChar(50))
INSERT INTO @Table SELECT 1, '"The morning..."'
INSERT INTO @Table SELECT 2, 'The long and...'
INSERT INTO @Table SELECT 3, '-ABC of Math'
INSERT INTO @Table SELECT 4, '*Something Else'
SELECT * FROM @Table
ORDER BY (SELECT CASE WHEN (UNICODE(SUBSTRING(Name,1,1))< 123)
THEN UNICODE(SUBSTRING(Name, 2,1))
ELSE Name End) ASC
Notice how
"The morning..."
appears before
The Long and...
Thanks again for your example
Alexei Rodriguez
|
|
|
|
|
Yes, I have tried quite a few things. You will have to parse the data in the client and perform your sorting there. This is the correct way. The database is for data and not particularly interested in the formatting.
I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
AlexeiXX3 wrote: Notice that the title may o may not have or may have more than 1 special character at the beggining
The best I can think of is a UDF that uses the REPLACE function to strip those characters. You could add this on a separate view on the table, including the column only when needed- as it would be a very expensive operation (looping all characters for each record).
It might be better (performance-wise) to store a redundant version of the Title, without any special characters.
I are Troll 
|
|
|
|
|
hy,
I have got data in form of pdf file(in table format) i want to extract data and dump it in database
Is there any way in which i can read data from pdf file.
|
|
|
|
|
Hi!
I need to move a database from one server to another.
So I have the server 'S1', the database 'DB1' which is located on the server 'S1' and the server 'S2'. I use MS SQL Server 2005 Express.
I carried out following actions:
1) executed stored procedure sp_detach_db 'DB1' on server 'S1'
2) copied files DB1.mdf and DB1Log.ldf from 'S1' to 'S2'
3) tried to execute the query on the server 'S2':
CREATE DATABASE DB1
ON (NAME = DB1_data, FILENAME = 'file_path\DB1.mdf')
LOG ON (NAME = DB1_log, FILENAME = 'file_path\DB1Log.ldf')
FOR ATTACH
and recieved the error: "Unable to open the physical file "file_path\DB1.mdf Operating system error 5".
When I executed the same query on 'S1' to attach DB1 again it was finished successfully...
Tell me please what is the source of the error? and how do I solve my problem.
|
|
|
|
|
Problem is solved by adding full access permisions for 'MSSQLUser' user group to the file_path directory.
|
|
|
|
|
Dears,
I have problem in geting record from SQL SERVER 2000
I am using stored procedure with (READPAST) in query
Ex select top 100 * From T_table with (READPAST)
and im using lock for some record
i want to get unlocked records using READPAST
and get error in .net
Message: You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.
Yes. CreatiVity withOuT limiTs
|
|
|
|
|
I don't no where to post this question please help me
I am facing this problem from last two days
while opening report from my website i am getting following issue this was working fine on our test server
but in client machine we are getting this issue
System.Security.SecurityException: Request for the permission of type 'System.Web.AspNetHostingPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
i am using vs2005 and sql2005 reporting services
i checked on client machine report is opening from report manager
and anonymous user chek box is checked and windows auth check box checked
we have one user reportadmin he has rights browser also and this user also have rights in reportserver db with resexe role .
this id my cod
protected void Page_Load(object sender, EventArgs e)
{
BreadCrumbs.MenuItemID = ModuleID;
HdnParameters.Value = Convert.ToString(Session["UserID"]) + "-" + Convert.ToString(Session["ProviderID"]) + "-" + ViewDataAccessType;
RVReferralListing.BorderWidth = 0;
if (!this.IsPostBack)
{
int referralID = 0;
if (Request.QueryString["ReferralID"] != null)
{
referralID = Convert.ToInt32(Request.QueryString["ReferralID"]);
FillSubReport(referralID);
}
if (referralID == 0)
{
PnlSearchBasedControls.Attributes.Add("style", "display:''");
divMainReport.Attributes.Add("style", "display:''");
divSubReport.Attributes.Add("style", "display:none");
}
else
{
PnlSearchBasedControls.Attributes.Add("style", "display:none");
divMainReport.Attributes.Add("style", "display:none");
divSubReport.Attributes.Add("style", "display:''");
}
Session["ClientAccessData"] = Convert.ToString(Session["UserID"]) + "-" + Convert.ToString(Session["ProviderID"]) + "-" + ViewDataAccessType;
}
}
private void FillSubReport(int referralID)
{
RVReferralSubReport.Visible = true;
RVReferralSubReport.BorderWidth = 2;
Uri ur = new Uri(Convert.ToString(ConfigurationManager.AppSettings["ReportServerUri"]));
RVReferralSubReport.ServerReport.ReportPath = Convert.ToString(ConfigurationManager.AppSettings["ReportPath"]) + "/ChldReferralServicesReport";
RVReferralSubReport.ServerReport.ReportServerUrl = ur;
List<ReportParameter> paramList = new List<ReportParameter>();
paramList.Add(new ReportParameter("ReferralID", referralID.ToString(), false));
RVReferralSubReport.ServerReport.SetParameters(paramList);
}
protected void btnViewReport_OnClick(object sender, EventArgs e)
{
string clientID = "";
string stateID = "";
if (txtClientName.Text.Trim() != "")
{
if (CheckIfExists_ClientName(txtClientName.Text) == "Empty")
{
hdnClientIDTyped.Value = "";
lblWarning.Text = "Please select valid client name from the list.";
ScriptManager.GetCurrent(Page).SetFocus(txtClientName);
txtClientName.Text = "";
MPEWarning.Show();
return;
}
else
{
hdnClientIDTyped.Value = CheckIfExists_ClientName(txtClientName.Text);
}
clientID = hdnClient.Value.ToString() != "" ? hdnClient.Value : hdnClientIDTyped.Value;
}
if (txtState.Text.Trim() != "")
{
if (CheckIfExists_State(txtState.Text) == "StateError")
{
hdnClientIDTyped.Value = "";
lblWarning.Text = "Please select valid state from the list.";
ScriptManager.GetCurrent(Page).SetFocus(txtState);
txtState.Text = "";
MPEWarning.Show();
return;
}
else
{
hdnStateTypedID.Value = CheckIfExists_State(txtState.Text);
}
stateID = hdnState.Value.ToString() != "" ? hdnState.Value : hdnStateTypedID.Value;
}
FillReport(clientID != "" ? clientID : null, stateID != "" ? stateID : null, txtZipCode.Text != "" ? txtZipCode.Text : null);
}
private void FillReport(string clientID, string stateID, string zipCode)
{
RVReferralListing.Visible = true;
RVReferralListing.BorderWidth = 2;
Uri ur = new Uri(Convert.ToString(ConfigurationManager.AppSettings["ReportServerUri"]));
RVReferralListing.ServerReport.ReportPath = Convert.ToString(ConfigurationManager.AppSettings["ReportPath"]) + "/PG_ReferralReport";
RVReferralListing.ServerReport.ReportServerUrl = ur;
List<ReportParameter> paramList = new List<ReportParameter>();
paramList.Add(new ReportParameter("ClientID", clientID, false));
paramList.Add(new ReportParameter("State", stateID, false));
paramList.Add(new ReportParameter("Zipcode", zipCode, false));
paramList.Add(new ReportParameter("ZipCode_1", zipCode, false));
paramList.Add(new ReportParameter("UserID", Convert.ToString(Session["UserID"]), false));
paramList.Add(new ReportParameter("AllowView", Convert.ToInt32(AllowView).ToString(), false));
paramList.Add(new ReportParameter("AllowEdit", Convert.ToInt32(AllowEdit).ToString(), false));
paramList.Add(new ReportParameter("EditDataAccessType", EditDataAccessType, false));
paramList.Add(new ReportParameter("ViewDataAccessType", ViewDataAccessType, false));
RVReferralListing.ServerReport.SetParameters(paramList);
//RVReferralListing.Reset();
hdnClient.Value = "";
hdnState.Value = "";
}
|
|
|
|
|
I got the solution for above issue
Issue is we have a folder name called Reports in application and also we have same name virtual directory in IIS
|
|
|
|
|
Hi,
DECLARE @Location_ID INT<br />
SELECT @Location_ID = 3<br />
<br />
;WITH ret AS(<br />
SELECT * FROM Locations<br />
WHERE Location_ID = @Location_ID <br />
UNION ALL<br />
SELECT t.*<br />
FROM Locations t INNER JOIN<br />
ret r ON t.Parent_ID = r.Location_ID<br />
)<br />
SELECT * FROM ret
here is my query, am getting error:Incorrect syntax near the keyword 'WITH'
help me to fix this problem friends
icanmakeiteasy
|
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
