|
Hey guys, need to format a column of results to display as columns in another query.... let me explain a little better *hopefully*
in the result set below notice how the empID is repeated in some cases
ID empID phone_num displayTel type
----------- ----------- ------------ ---------- -----------
159 625 123-123-4567 1 1
163 625 465-456-4657 1 1
154 626 944-789-4515 1 1
155 626 984-321-4567 1 2
150 639 984-266-3336 1 1
145 802 465-465-7894 1 5
146 802 654-798-4657 1 6
165 853 132-123-1324 1 1
i need to beable to separate the "phone_num" data based on the "type" and then display that data in-line on another query
i.e.
Not This!!!
Name empID phone_num activeUser contact
----------- ----------- ------------ ---------- -----------
Demo User 625 123-123-4567 1 1
Demo User 625 465-456-4657 1 1
Instead Like This!!
Name empID phone_num1 phone_num2 activeUser contact
----------- ----------- ------------ ------------ ---------- -----------
Demo User 625 123-123-4567 465-456-4657 1 1
I need to be able to do this all at once while i'm also quering about 15 other tables.... so i need to be able to do it in-line or subqueried...
my only idea would be to do something like this ('course it won't work))
<br />
SELECT col1, col2, (if (select phone_num from phone where empID = @empIDNum) > 1 <br />
{<br />
for i = 1 to (select count(phone_num)) <br />
select top (i) phone_num as phone_num+(i) from Phone where empID = @empIDNum <br />
Next}<br />
)<br />
else<br />
{<br />
select phone_num from phone where empID = @empIDNum<br />
}<br />
from employees<br />
thanks!!!
string Beautiful;
Beautiful = "ignorant";
label1.Text = "The world is full of " + Beautiful +" people.";
Why is common sense such an un-common comodity?
|
|
|
|
|
Can you use a stored procedure?
As far as I know, you cannot do a cross-tab sql statement in SQL Server. There are ways to do it in a stored procedure though.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
EricDv,
Yes I can use a SP, my ultimate goal out of all this is to setup a dataset, for select and update
string Beautiful;
Beautiful = "ignorant";
label1.Text = "The world is full of " + Beautiful +" people.";
Why is common sense such an un-common comodity?
|
|
|
|
|
It isn't pretty and I don't know how it will perform, - but here is one solution:
DECLARE @iColumns INT, @iCounter INT, @sql VARCHAR(2500)
SET @iCounter = 1
SET @sql = ''
select @iColumns = (SELECT TOP 1 COUNT(*) AS cnt FROM phonenumbers GROUP BY empid ORDER BY cnt DESC)
WHILE (@iCounter <= @iColumns)
BEGIN
SET @sql = @sql + ', Phone' + CAST(@iCounter AS VARCHAR) + ' VARCHAR(12)'
SET @iCounter = @iCounter + 1
END
SET @sql = 'CREATE TABLE #CrossPhones (empid INT' + @sql + ');'
+ 'DECLARE @sql2 VARCHAR(100),@iCounter INT,@iEmpIDHolder INT, @iEmpID INT, @phone_num VARCHAR(12), @teltype INT;'
+ 'DECLARE curNumbers CURSOR FOR SELECT empid,phone_num,teltype FROM phonenumbers p ORDER BY empid,teltype;'
+ 'OPEN curNumbers;'
+ 'FETCH NEXT FROM curNumbers INTO @iEmpID, @phone_num, @teltype;'
+ 'WHILE @@FETCH_STATUS = 0'
+ 'BEGIN'
+ ' SET @iEmpIDHolder = @iEmpID;'
+ ' INSERT INTO #CrossPhones (empid) VALUES (@iEmpID);'
+ ' SET @iCounter = 0;'
+ ' WHILE @iEmpIDHolder = @iEmpID'
+ ' BEGIN'
+ ' SET @iCounter = @iCounter + 1;'
+ ' SET @sql2 = ''UPDATE #CrossPhones SET Phone'' + CAST(@iCounter AS VARCHAR) + ''='''''' + CAST(@phone_num AS VARCHAR) + '''''' WHERE empid='' + cast(@iEmpID as VARCHAR);'
+ ' EXEC(@sql2);'
+ ' FETCH NEXT FROM curNumbers INTO @iEmpID, @phone_num, @teltype;'
+ ' IF @@FETCH_STATUS != 0 BREAK;'
+ ' END;'
+ 'END;'
+ 'CLOSE curNumbers;'
+ 'DEALLOCATE curNumbers;'
+ 'SELECT e.name,c.* FROM #CrossPhones c INNER JOIN Employees e ON c.empid = e.empid;'
EXEC(@sql)
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Alias your phone table and join to employees twice. Use a left join so you are sure to get every employee even if they don't have the certain type of phone number.
SELECT
Name,
empID,
a.phone_num as phone_num1,
b.phone_num as phone_num2,
activeUser,
contact
FROM
employees
LEFT JOIN
phone a
ON (employees.empID = a.EmpID AND
a.type = 1)
LEFT JOIN
phone b
ON (employees.empID = b.EmpID AND
b.type = 2)
You can also use a correlated sub query. This method won't be as fast as the first method.
SELECT
Name,
empID,
(SELECT TOP phone_num
FROM Phone
WHERE empId = employees.empId AND
type = 1) as phone_num1,
(SELECT TOP phone_num
FROM Phone
WHERE empId = employees.empId AND
type = 2) as phone_num2,
activeUser,
contact
FROM
employees
-- modified at 14:52 Thursday 25th May, 2006
|
|
|
|
|
Michael,
Thank you for your reply,
I find your soultion of using the multiple joins interesting...
however I have a question on it, the possiblity exist that there will be an unnumerable amount of phone_num results available for each user. (while the truth is we "attempt" to limit it to 4) How would you propose doing the multiple joins for x number of results?
My inital thought would be to use a while loop to loop thru all the results. but doing that still leaves the question of how to deal with the multiple results of the intial query...
for instance when you do a left join on these tables, because there are say 4 instances of a phone_num in the phone table per user, then my list of users (say 100) will return with 400 results. even if i do the multilple joins on the same table i'll still have 400 results from the inital query, since the join doesn't consume the results in any way.
--unless there is a way to include the TOP statement.... hmm, i'll look further into that....
If I figure that out then I'll post my resolution. -- 'course if you already know the resolution I'll happly accept assistance 
string Beautiful;
Beautiful = "ignorant";
label1.Text = "The world is full of " + Beautiful +" people.";
Why is common sense such an un-common comodity?
|
|
|
|
|
Here is another solution, but with this no employee can have two of the same phone type:
DECLARE @iColumns INT, @sql VARCHAR(2500),@cTypeID VARCHAR(10)
SET @sql = ''
DECLARE curTypes CURSOR FOR SELECT DISTINCT CAST(teltype AS VARCHAR(10)) FROM PhoneNumbers
OPEN curTypes
FETCH NEXT FROM curTypes INTO @cTypeID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ', (SELECT phone_num FROM PhoneNumbers p WHERE p.empid = e.empid AND p.teltype = ' + @cTypeID + ') as Type' + @cTypeID
FETCH NEXT FROM curTypes INTO @cTypeID
END
close curTypes
DEALLOCATE curTypes
SET @sql = 'SELECT e.name,e.empid' + @sql + ' FROM employees e'
exec(@sql)
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Hi,
Does anybody knows the MS Access equivalent for IS_Member(Role) in SQl.
The Is_Member() specifies wether the db user belongs to the group Role or not. if anyBody knows how to do this with MS Access please help.Any other suggestion is welcome
Thanks all
|
|
|
|
|
I've switched a project from using an SQL Express database to an Access database, and I'm finding a number of issues with the differences in SQL Syntax. Does anyone know of a good website that describes functions, syntax, etc. for the type of SQL that Access uses? What I am currently stuck on is how to convert an integer into a string in a select statement. This was the way I did it for SQL Express:
SELECT STR(myField, 10, 0) FROM myTable
But now with Access I'm getting an error about the number of parameter's I'm using, so Access must do things differently.
I would appreciate any help.
|
|
|
|
|
Kschuler wrote: how to convert an integer into a string in a select statement
SELECT CSTR(myField) AS myField FROM myTable
Don't know a good site for MSAcess SQL syntax.
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Hi All,
In C#, to get the time you use DateTime.Now, and if you want just the date you can use DateTime.Today which returns you the time at Midnight this morning.
I need to set a DateTime variable in the same manner in Sql Server with 'Today'. I could do this by somehow lopping of the fractional part, by converting it to a float, flooring it, then converting it back to a DateTime or something like that, but this seems somewhat hideous to me.
Am I missing a simple solution to this? Help as always appreciated. 
Regards,
Rob Philpott.
|
|
|
|
|
Here is one way:
DECLARE @dtVar DATETIME
SELECT @dtVar = CONVERT(VARCHAR, GETDATE(),101)
PRINT @dtVar
you could CAST(CONVERT(varchar, GETDATE(),101) AS DATETIME) but i don't know if it is necessary...i think it is automatic
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Thanks Eric, that looks pretty good.
I've never quite got the hang of the date conversion functions with the arbitary number (101) denoting something. But does the tricky perfectly!
Regards,
Rob Philpott.
|
|
|
|
|
Rob Philpott wrote: I've never quite got the hang of the date conversion functions with the arbitary number (101) denoting something.
It isn't quite arbitrary. 101 is a parameter for USA standard. It might be better to use 112, which is the ISO standard. But, everything I've developed so far is for the US only, so I don't know much about international standards.
Colin Angus Mackay and I had a little exchange about similar date stuff a few posts down: http://www.codeproject.com/script/comments/forums.asp?forumid=1725&select=1499243#xx1499243xx[^]
----
For more info, look up CAST and CONVERT in SQL Server Books Online.
In the table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).
Without century (yy) With century (yyyy)
- 0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - Mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 (*) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 (*) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces)
- 130* Kuwaiti dd mon yyyy hh:mi:ss:mmmAM
- 131* Kuwaiti dd/mm/yy hh:mi:ss:mmmAM
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
I finally have an understanding of how to create simple reports from my dataset using the ReportViewer. I now wonder how I can make custom (SQL-queries) on my dataset that will fill for e.g. a table.
For example I have a picture that I like to insert into my report. That picture do I store as a string in my database. (In my program I do conversions functions for: Image -> byte[] -> string -> byte[] -> Image).
_____________________________
...and justice for all
APe
|
|
|
|
|
Hi,
We are creating an ASP Database Application that connects to Sql Server 2000.
The application is for associating a zip code with a warehouse. For instance a zip code for the los angeles area would go to the los angeles warehouse and a zip code for the new york area would go to the new
york warehouse. We would like to enter zip code ranges into the following Wzip_Log table. Then we would like to programmatically populate the Wzip_Detail table and the Wzip table.
The Wzip_Log table holds zip code ranges, where as the Wzip_Detail and the Wzip table hold individual zip codes.
For instance, the Wzip_Log table might have the following record (completely fictitious)
Wzip_Log_ID: 1
PostalCode_Start: 11111
PostalCode_End: 11113
CountryCode: USA
WarehouseNum: 1
Operator: jdoe
Time_Stamp: 00:00:00 6/1/2006
Then we would want the Wzip_Detail table to be populated as follows:
Wzip_Detail_ID: 1
Wzip_Log_ID: 1
PostalCode: 11111
CountryCode: USA
WarehouseNum: 1
Wzip_Detail_ID: 2
Wzip_Log_ID: 1
PostalCode: 11112
CountryCode: USA
WarehouseNum: 1
Wzip_Detail_ID: 3
Wzip_Log_ID: 1
PostalCode: 11113
CountryCode: USA
WarehouseNum: 1
The Wzip table would be populated exactly the same as the Wzip_Detail table at this point.
However, say a user later updated the Wzip_Log table as follows:
Wzip_Log_ID: 2
PostalCode_Start: 11111
PostalCode_End: 11113
CountryCode: USA
WarehouseNum: 2
Operator: jdoe
Time_Stamp: 01:10:00 6/4/2006
The Wzip_Detail table would contain an INSERT of this data (giving it a total of 6 records)
whereas the Wzip table would contain an UPDATE of this data (giving it a total of 3 records)
For your reference, here are the table layouts:
Table Name: Wzip_Log
Wzip_Log_ID int 4 (primary key, Identity)
PostalCode_Start varchar 10
PostalCard_End varchar 10
CountryCode varchar 3
WarehouseNum int 4
Operator varchar 50
Time_Stamp datetime 8
Table Name: Wzip_Detail
Wzip_Detail_ID int 4 (primary key, Identity)
Wzip_Log_ID int 4 (foreign key)
PostalCode varchar 10
CountryCode varchar 3
WarehouseNum int 4
Table Name: Wzip
Wzip_ID int 4 (primary key, Identity)
Wzip_Log_ID int 4 (foreign key)
Wzip_Detail_ID int 4 (foreign key)
PostalCode varchar 10
CountryCode varchar 3
WarehouseNum int 4
I think there is probably some way to do this programmatically in Sql Query Analyzer, but are not sure how, so I thought I would ask you here who have helped me tremendously in the past.
I am eager to learn more about writing such functions and am enjoying this project.
Thanks in advance for any help you can give,
Anne
|
|
|
|
|
Problem!!! i am creating database connection using VC++ and SQL server with authentication 'sa' and password "***". I am using ADO connetion (with DSN).
I have two accounts on my computer system Administrator and Operator.
1- Administrator have full access on computer system.
2- Operator have limited access in computer system (can't perform installation)
Well I can successfully connect with database from administrator account.
Buttt when i try to run my application from operator account then then database connection failed.
Would some body help me... Its urgent.
|
|
|
|
|
My colleague is not able to access the sql server that resides in my local system from the asp.net application in her pc even though my sql server is running.
She is able to access my sql server using her query analyser.
The code in web.config
add key="ConnectionString" value="Data Source=Myservername;User Id=mysqlservername;
Password=mysqlserverpassword;Initial Catalog=mydatabasename;Connection Lifetime=30"
I have used the same code in my web.config and i am able to access my sql server.
Do tell if i have to give some rights for her to access my sql server through asp.net application
-- modified at 6:59 Tuesday 23rd May, 2006
|
|
|
|
|
Check sql server authentication mode in your Enterprise manager. Try changing it to work in both windows and sql server mode.
|
|
|
|
|
Thanks for the reply.
The authentication mode is SQL Server and Windows.
|
|
|
|
|
Hi,
I need to do update operation for a set of ids, for this i need to use comma separator how can do it
I am giving like this
update Emp_Tbl set empstatus=1 where empid in (@empids)
is it enough or any other methods
|
|
|
|
|
This will work 
================
DECLARE @empids varchar(100)
SET @empids= '66696, 66695, 66694, 66693, 66692'
exec('update Emp_Tbl set empstatus=1 where empid in (' + @empid + ')')
Look where you want to go not where you don't want to crash.
Bikers Bible
-- modified at 6:46 Tuesday 23rd May, 2006
|
|
|
|
|
1)OLEDB Connection Pooling is handled by OLEDB.Net Provider and SQLClient Connection pooling is handled by____
2)If the DSN (Data Source Name) has to be used with SQL Server, which namespace has to be imported?
3)If the DSN (Data Source Name) has to be used with SQL Server, which namespace has to be imported?
4)Which object in ADO.Net is similar to the Recordset object of ADO?
plz let me konw the ans
witn regards
d.ravi kiran reddy
|
|
|
|
|
hi,
This is Dayakar
You need to add following namespaces for the specified data providers:
Data Provider Namespace
MS SQL Server System.Data.SqlClient
Oracle Database System.Data.OracleClient
OLE DB Databases System.Data.OleDBClient
ODBC Data Sources System.Data.ODBCClient
|
|
|
|
|
Hi,
I want to make one column as a index in one table. i.e how to make one column as a primary key at run time
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
