Return single colum from multiple rows into one row?
Often in my apps I'll create a table in SQL to house different variables. I do this so that I don't end up with multiple tables just to store a list of phone types for example. What I mean is let's say I have a form that would store contact information one of the controls might be a phone number and then next to that I'd have a drop down that would list the different phone types like Phone, Mobile, Fax etc rather than hard coding a specific textbox for only phone or only the mobile number. This way the user can determine white type of contact number it is.
With that in mind I'd like to pull out of the table only specific values from a table's colum but return them as one row. Let me give you an idea of what I mean.
Here is your table:
Here is what the data would be:
As you can see the type defines a certain type of data in the case above a Mail Setting. Then the name of the variable for example the SMTP Host name, then its's value. So rather than create tables for every set of lists an app may contain I do it this way so I don't end up with a table with only 5 or 6 rows in it just for Mail Settings or a table for say themes to the web site etc.
Normally I just pull out a specific record's value by passing the type and name parameters to get a value. Easy enough and usually that is all I'm doing. But in the case of the above I would rather pull out all mail settings as a single row so that I can then populate some textboxes.
Basically create a statement that would return the data like this:
SMTPHost SMTPPort SMTPFrom SMTPAuth SMTPUsername SMTPPassword
myhost 25 x@xyz.com 0 username password
Not that it matters but the AUTH column just tells whether or not the server requires credentials to relay. Basically 0 = None and 1 = credentials needed.
Please don't reply with "use App settings" or use built in SMTP web config settings as in this app I can't do that for reasons that would be too lengthy to discuss.
Bottom line I'm trying to avoid building a function that calls each variable 1 by one to then populate a text box for each value. Maybe there isn't a better way to do this but just seemed like there might be. Any ideas are greatly apprecieated.
|
0 |
|
chazelton
2/7/2009 12:10:08 AM
asp.net.sql-datasource
29906 articles. 
0 followers.
3 Replies
757 Views
If you are using SQL Server 2005 or higher, look into the PIVOT command.
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData
When I answer your question successfully, you are helping me grow by asking it.
|
|
0 |
|
ivanpro
2/7/2009 4:05:18 AM
Yeah I thought that might be the solution. was just hoping somone had done it and have a snipit of code.
|
|
0 |
|
chazelton
2/7/2009 5:09:45 PM
declare @tablemails table (id int identity(1,1), type nvarchar(50), name nvarchar(50), value nvarchar(50)) INSERT INTO @tablemails SELECT 'MailSetting','SMTPHost ','myhost' UNION ALL SELECT 'MailSetting','SMTPPort ','25' UNION ALL SELECT 'MailSetting','SMTPFrom','x@xyz.com' UNION ALL SELECT 'MailSetting','SMTPAuth','0' UNION ALL SELECT 'MailSetting','SMTPUsername','usernameJoe' UNION ALL SELECT 'MailSetting','SMTPPassword','userSecret' SELECT [SMTPHost],[SMTPPort],[SMTPFrom],[SMTPAuth],[SMTPUsername],[SMTPPassword] FROM (SELECT type, name, value FROM @tablemails) src PIVOT (MAX(value) FOR name IN ([SMTPHost],[SMTPPort],[SMTPFrom],[SMTPAuth],[SMTPUsername],[SMTPPassword])) pvt
Limno
|
|
0 |
|
limno
2/7/2009 5:23:33 PM
Similar Artilces:
SQL: mutiple rows one column to multiple columns one row
In InfoMaker SQL can you take a table with multiple rows for the same key and take a column from the rows and create mutiple columns with one report line with the key(no duplicate keys) on a report? For example Table books (key) (book title) 123 misery 123 titanic 123 roots 456 war 456 1984 456 giant The report I need would look like this Key title1 title2 title 3 123 misery titanic roots 456 war 1984 giant Thanks in advance. -- Kaye Hendry HealthInsight email:kaye.hendry@healthinsight.org ...
single-row subquery returns more than one row
I want a subquery to return more than one value then use those values to get more values from the other table. But I am receiving the errors saying ORA-01427: single-row subquery returns more than one row. How can I avoid this and achieve want I want? My sql and Csharp is below:dbcon.Open(); string strreg = "SELECT Amount AS TotalAmount FROM Payment WHERE studentno=(SELECT tempcode FROM specialstudent WHERE custid='"+ AreaNO +"')"; &...
need help with ORA-01427: single-row subquery returns more than one row error
the following select in oracle 10g gives an error , need some help thanks select 'AGD' BusUnitCode, 'A' LedgerCode, 'ACTUALS' LedgerName, (select sign(count(OBJECT_NAME)) from ALL_OBJECTS where OBJECT_ID = (select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME='SALHARCAGD' ) ) LedgerHasArchive, ' ' DataAccessGroup from ALL_OBJECTS where (select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME='SALFLDGAGD'and DATA_OBJECT_ID is not null ) is not null unionselect 'AGD', substr(KEY_FIELDS,1,1), substr(SUN_DATA,1,15), 0, substr(SUN_DATA...
Combine Multiple Rows into One Row
I am having a problem joining multiple row values into a single row.I need the values to be displayed like:Values Merged Value Equals123 - 127 2129 2131 2 Currently the values are displaying like this:Values Not Merged &nb...
concatenate multiple rows in one row
i have a table below id text1 text2 -------------------------------118 id12 text123 7118 4 retre 8118 3 erwetre i need to convert the rows to columns in sql server 2000(pivot in 2005 but i dont want in 2005) to id text1 text2 text1 text2 text1 &...
single row query rteturn more than one row
aSBnb3QgZm9sbG93aW5nIG1lc3NhZ2Ugd2hlbiBpIHJ1biBzcWwgc3RhdGVtZW50IGFzIHNx bA0KDQpzcWw9c2VsZWN0IHVwX3RpbWUgZnJvbSB1cHRpbWUgd2hlcmUNCiAgICAgICAgICAg ICBzdGFydGRhdGUgPSAoc2VsZWN0IEdSRUFURVNUKHN0YXJ0ZGF0ZSkNCiAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICBmcm9tIHVwdGltZSB3aGVyZQ0KICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgIGhvc3RuYW1lID0gJ3R3aXN0ZXInKQ0KREJEOjpPcmFj bGU6OnN0IGV4ZWN1dGUgZmFpbGVkOiBPUkEtMDE0Mjc6IHNpbmdsZS1yb3cgc3VicXVlcnkg cmV0dXJucw0KbW9yZSB0aGFuIG9uZSByb3cgKERCRCBFUlJPUjogT0NJU3RtdEV4ZWN1dGUp IGF0IHVwZ2V0LnBsIGxpbmUgMzUuDQoNCk9SQS0wMTQyNzogc2luZ2xlLXJvdyBzdWJxdWVy eSByZXR1...
how to display multiple rows of a table in single row
DECLARE @emp VARCHAR(1024) declare @emp1 varchar(1024)declare @emp2 varchar(1024)SELECT @emp1 = COALESCE(@emp1 + ',', '') + cast(eid as varchar(10)),@emp = COALESCE(@emp + ',', '') + ename ,@emp2 = COALESCE(@emp2 + ',', '') + desigFROM emp SELECT eid=@emp1,ename = @emp,desig=@emp2 anitha123:DECLARE @emp VARCHAR(1024) declare @emp1 varchar(1024)declare @emp2 varchar(1024)SELECT @emp1 = COALESCE(@emp1 + ',', '') + cast(eid as varchar(10)),@emp = COALESCE(@emp + ',', '') + ename ,@emp2 = COALESCE(@emp...
How do i get one row if the resultset returned has no rows.
Hi I have couple of CTE's which i want to ultimately insert into a temp table... and based on my condition some of the CTE's return some rows and some dont... but i want to have the CTE return atleast one row if there is no data in it.. I have tried doing the following but i am not getting the desired output ;with cte_rpt4 as (select '4' a,recd_dt as approve_dt, prov_no, prov_name, client_id, claim_mst_id, claim_adj_id, claim_dt, claim_amt, rpt4, (Select count(distinct(Claim_mst_Id)) as crpt4 from #temp1 where datediff(day,isnull(recd_dt,create_dt),isnull(c...
convert multiple row values into single row
Hi All, I have to convert multiple row values into single row with multiple columns, can anybody help me in this regard. Thanks in advance. can u provide some information. i didn't get ur requirement properly. Clarity is needed.Thank uBabaPlease remember to click "Mark as Answer" on this post if it helped you. Hi, Maybe a Pivot Query is what you're looking for? Greg...
UPDATE multiple ROWS with different Values with single statement ?- Multiple Rows in subquery- ORA-01427
Hi I'm hoping one of you kind folk can help with this. I want to UPDATE a field of table with the results of another query, (sub query), but my sub quesry contains a Group By and so returns multipel rows.UPDATE TABLE_ONE SET TOTAL_SALES =(SUBQUERY) The Subquery: Select COUNT(*) from TABLE_TWO where TRANSACTION_TYPE="SALE" GROUP BY PERSON_ID QUERIES togetherUPDATE TABLE_ONESET TOTAL_SALES=(SELECT Count(*) FROM TABLE_TWO GROUP BY PERSON_ID) ThisSubquery return multiple rows and hence teh ORA-01427 error.Can anyone tell me how to loop thr...
Gridview Rows stretching in size when one row is returned
HiI hope this is a trival problem.My problem with the gridview is that when one row or only a few rows are returned to the gridview the spacing round the data in the row(s) increases. If there are many more rows, say 10 or more, the spacing is how I want it to be. I have tried setting the spacing for the rows in the properties of the row, but with no luck.Does any one have a way of fixing the row spaces regardless of the number of rows returned? Thanks Hi AndyMoireASP, It seems you have resolved the issue. If so, please post the solution and share with us. Thanks,Qin Dian TangMicro...
xml export rows as with template
Ive created a datawindow with a template. Now when i do a save as, it only returns one row? I am expecting all of the rows , using the template I have defined. > Ive created a datawindow with a template. Now when i do a > save as, it only returns one row? I am expecting all of > the rows , using the template I have defined. found the solution. Start Detail option!!! ...
Display multiple table rows in a single Gridview row?
I have two tables: transportreply |id| |submitby| |transport| 2008 |id| |liftclub| |liftclubsize| |workfromhome| |leave| |submitby| In the transportreply, there are multiple rows with information submitted by the same user originating from a checkboxlist. What I need is an inner join of the two tables that display all the results on one Gridview row. Currently my code looks like this: Results.aspx<table align="center" width="900" bgcolor="#E6E6E6" class="table3"><tr><td><div class="td4&q...
Single row db call, multiple row populate
I am attempting to populate multiple rows of a datagrid from a single row in a database. So, say that I am needing columns 2-5 from row 1. I want the data from line 2 on one datagrid row, and the data from line 3 on the next row, etc... Is this possible or am I trying to reinvent the wheel? it is possible (you will have to do quite a bit of data manipulation after the data is extracted from the database) but conceptually it would be a lot confusing. Consider storing data shown on different lines in separate records. If this does not help, please explain your requirements in more ...
In InfoMaker SQL can you take a table with multiple rows for the same key and take a column from the rows and create mutiple columns with one report line with the key(no duplicate keys) on a report? For example Table books (key) (book title) 123 misery 123 titanic 123 roots 456 war 456 1984 456 giant The report I need would look like this Key title1 title2 title 3 123 misery titanic roots 456 war 1984 giant Thanks in advance. -- Kaye Hendry HealthInsight email:kaye.hendry@healthinsight.org ...
single-row subquery returns more than one row
I want a subquery to return more than one value then use those values to get more values from the other table. But I am receiving the errors saying ORA-01427: single-row subquery returns more than one row. How can I avoid this and achieve want I want? My sql and Csharp is below:dbcon.Open(); string strreg = "SELECT Amount AS TotalAmount FROM Payment WHERE studentno=(SELECT tempcode FROM specialstudent WHERE custid='"+ AreaNO +"')"; &...
need help with ORA-01427: single-row subquery returns more than one row error
the following select in oracle 10g gives an error , need some help thanks select 'AGD' BusUnitCode, 'A' LedgerCode, 'ACTUALS' LedgerName, (select sign(count(OBJECT_NAME)) from ALL_OBJECTS where OBJECT_ID = (select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME='SALHARCAGD' ) ) LedgerHasArchive, ' ' DataAccessGroup from ALL_OBJECTS where (select OBJECT_ID from ALL_OBJECTS where OBJECT_NAME='SALFLDGAGD'and DATA_OBJECT_ID is not null ) is not null unionselect 'AGD', substr(KEY_FIELDS,1,1), substr(SUN_DATA,1,15), 0, substr(SUN_DATA...
Combine Multiple Rows into One Row
I am having a problem joining multiple row values into a single row.I need the values to be displayed like:Values Merged Value Equals123 - 127 2129 2131 2 Currently the values are displaying like this:Values Not Merged &nb...
concatenate multiple rows in one row
i have a table below id text1 text2 -------------------------------118 id12 text123 7118 4 retre 8118 3 erwetre i need to convert the rows to columns in sql server 2000(pivot in 2005 but i dont want in 2005) to id text1 text2 text1 text2 text1 &...
single row query rteturn more than one row
aSBnb3QgZm9sbG93aW5nIG1lc3NhZ2Ugd2hlbiBpIHJ1biBzcWwgc3RhdGVtZW50IGFzIHNx bA0KDQpzcWw9c2VsZWN0IHVwX3RpbWUgZnJvbSB1cHRpbWUgd2hlcmUNCiAgICAgICAgICAg ICBzdGFydGRhdGUgPSAoc2VsZWN0IEdSRUFURVNUKHN0YXJ0ZGF0ZSkNCiAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICBmcm9tIHVwdGltZSB3aGVyZQ0KICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgIGhvc3RuYW1lID0gJ3R3aXN0ZXInKQ0KREJEOjpPcmFj bGU6OnN0IGV4ZWN1dGUgZmFpbGVkOiBPUkEtMDE0Mjc6IHNpbmdsZS1yb3cgc3VicXVlcnkg cmV0dXJucw0KbW9yZSB0aGFuIG9uZSByb3cgKERCRCBFUlJPUjogT0NJU3RtdEV4ZWN1dGUp IGF0IHVwZ2V0LnBsIGxpbmUgMzUuDQoNCk9SQS0wMTQyNzogc2luZ2xlLXJvdyBzdWJxdWVy eSByZXR1...
how to display multiple rows of a table in single row
DECLARE @emp VARCHAR(1024) declare @emp1 varchar(1024)declare @emp2 varchar(1024)SELECT @emp1 = COALESCE(@emp1 + ',', '') + cast(eid as varchar(10)),@emp = COALESCE(@emp + ',', '') + ename ,@emp2 = COALESCE(@emp2 + ',', '') + desigFROM emp SELECT eid=@emp1,ename = @emp,desig=@emp2 anitha123:DECLARE @emp VARCHAR(1024) declare @emp1 varchar(1024)declare @emp2 varchar(1024)SELECT @emp1 = COALESCE(@emp1 + ',', '') + cast(eid as varchar(10)),@emp = COALESCE(@emp + ',', '') + ename ,@emp2 = COALESCE(@emp...
How do i get one row if the resultset returned has no rows.
Hi I have couple of CTE's which i want to ultimately insert into a temp table... and based on my condition some of the CTE's return some rows and some dont... but i want to have the CTE return atleast one row if there is no data in it.. I have tried doing the following but i am not getting the desired output ;with cte_rpt4 as (select '4' a,recd_dt as approve_dt, prov_no, prov_name, client_id, claim_mst_id, claim_adj_id, claim_dt, claim_amt, rpt4, (Select count(distinct(Claim_mst_Id)) as crpt4 from #temp1 where datediff(day,isnull(recd_dt,create_dt),isnull(c...
convert multiple row values into single row
Hi All, I have to convert multiple row values into single row with multiple columns, can anybody help me in this regard. Thanks in advance. can u provide some information. i didn't get ur requirement properly. Clarity is needed.Thank uBabaPlease remember to click "Mark as Answer" on this post if it helped you. Hi, Maybe a Pivot Query is what you're looking for? Greg...
UPDATE multiple ROWS with different Values with single statement ?- Multiple Rows in subquery- ORA-01427
Hi I'm hoping one of you kind folk can help with this. I want to UPDATE a field of table with the results of another query, (sub query), but my sub quesry contains a Group By and so returns multipel rows.UPDATE TABLE_ONE SET TOTAL_SALES =(SUBQUERY) The Subquery: Select COUNT(*) from TABLE_TWO where TRANSACTION_TYPE="SALE" GROUP BY PERSON_ID QUERIES togetherUPDATE TABLE_ONESET TOTAL_SALES=(SELECT Count(*) FROM TABLE_TWO GROUP BY PERSON_ID) ThisSubquery return multiple rows and hence teh ORA-01427 error.Can anyone tell me how to loop thr...
Gridview Rows stretching in size when one row is returned
HiI hope this is a trival problem.My problem with the gridview is that when one row or only a few rows are returned to the gridview the spacing round the data in the row(s) increases. If there are many more rows, say 10 or more, the spacing is how I want it to be. I have tried setting the spacing for the rows in the properties of the row, but with no luck.Does any one have a way of fixing the row spaces regardless of the number of rows returned? Thanks Hi AndyMoireASP, It seems you have resolved the issue. If so, please post the solution and share with us. Thanks,Qin Dian TangMicro...
xml export rows as with template
Ive created a datawindow with a template. Now when i do a save as, it only returns one row? I am expecting all of the rows , using the template I have defined. > Ive created a datawindow with a template. Now when i do a > save as, it only returns one row? I am expecting all of > the rows , using the template I have defined. found the solution. Start Detail option!!! ...
Display multiple table rows in a single Gridview row?
I have two tables: transportreply |id| |submitby| |transport| 2008 |id| |liftclub| |liftclubsize| |workfromhome| |leave| |submitby| In the transportreply, there are multiple rows with information submitted by the same user originating from a checkboxlist. What I need is an inner join of the two tables that display all the results on one Gridview row. Currently my code looks like this: Results.aspx<table align="center" width="900" bgcolor="#E6E6E6" class="table3"><tr><td><div class="td4&q...
Single row db call, multiple row populate
I am attempting to populate multiple rows of a datagrid from a single row in a database. So, say that I am needing columns 2-5 from row 1. I want the data from line 2 on one datagrid row, and the data from line 3 on the next row, etc... Is this possible or am I trying to reinvent the wheel? it is possible (you will have to do quite a bit of data manipulation after the data is extracted from the database) but conceptually it would be a lot confusing. Consider storing data shown on different lines in separate records. If this does not help, please explain your requirements in more ...
Web resources about - Return single colum from multiple rows into one row? - asp.net.sql-datasource
Multiple sequence alignment - Wikipedia, the free encyclopediaA multiple sequence alignment (MSA) is a sequence alignment of three or more biological sequences , generally protein , DNA , or RNA . In many ...
Nigeria: Over 70 Feared Killed in Borno, Adamawa Multiple BlastsMore than 70 people were feared killed in multiple bomb attacks believed to have been carried out by Boko Haram militants and their agents in ...
Facebook is testing multiple topic-based News Feeds for mobile devices... the information you want to see at the appropriate times. According to the screenshots taken by Jason Stein, Facebook looks to be testing multiple, ...
Multiple Jobholders Artificially Boost "Full-Time" Employment: Does the Sum of the Parts Equal the Whole ...... pretty much confirms what I have said about Obamacare boosting part-time employment at the expense of full time jobs. Please consider Multiple ...
Munich Police: Islamic State Planned New Year’s Eve Attack with Multiple Suicide BombersMunich police warned of a “serious, imminent threat” by Islamic State group suicide bombers wanting to commit an attack on New Year’s Eve.
Jim Cramer on balancing multiple jobs, his Brooklyn bar, and the Jon Stewart interview - Business Insider ...It was during law school that Cramer started recommending and trading stocks, which led to becoming a stockbroker at Goldman Sachs.
Miami teen shot multiple times at grandma's home
The victim reportedly remains in the hospital as police continue to search for the suspect
The victim reportedly remains in the hospital as police continue to search for the suspect
Watch Hero Philly Cop Get Shot Multiple Times And Then Give Chase
Watch Hero Philly Cop Get Shot Multiple Times And Then Give Chase
Watch Hero Philly Cop Get Shot Multiple Times And Then Give Chase
Facebook tests multiple News Feeds based on your interests... yearning for a way to cut through the clutter that is your Facebook News Feed, you may soon be in luck. The social network is testing multiple ...
Finally Tim Cook Wakes Up, Valuing Apple's App Store Using Netflix Multiple
Finally Tim Cook Wakes Up, Valuing Apple's App Store Using Netflix Multiple
Finally Tim Cook Wakes Up, Valuing Apple's App Store Using Netflix Multiple
Resources last updated: 1/11/2016 1:03:55 AM