April 22, 2012 at 11:26 pm
sir i have a table values like this
10 null null
null name null
null null hyd..
then i want to get in a single row sir.....
April 22, 2012 at 11:36 pm
Per the rules of this site, -
- please post the full DDL for your table;
- include some test data; and
-show the expected outcome.
It is darn difficult to help you if any of these is missing.
It is 10:35 PM of my local time but I will hang around for at least half an hour to help you if you provide the info I need.
April 22, 2012 at 11:45 pm
//this is my table
create table std_table
(
id int,
name nvarchar,
address nvarchar,
)
//this is my table data
10 null null
null anilkuar null
null null hyderabad
//i want the data like this
10 anilkumar hyderbad
April 22, 2012 at 11:47 pm
anil.janu143 (4/22/2012)
//this is my tablecreate table std_table
(
id int,
name nvarchar,
address nvarchar,
)
//this is my table data
10 null null
null anilkuar null
null null hyderabad
//i want the data like this
10 anilkumar hyderbad
Your table has exactly three rows of data, and will never ever have any more than that?
April 23, 2012 at 12:02 am
anil.janu143 (4/22/2012)
//this is my tablecreate table std_table
(
id int,
name nvarchar,
address nvarchar,
)
//this is my table data
10 null null
null anilkuar null
null null hyderabad
//i want the data like this
10 anilkumar hyderbad
Given nothing else for test data, the current solution would be...
SELECT MAX(ID), MAX(Name), MAX(Address)
FROM dbo.yourtable
If you'd like a more detailed answer, please see the first link in my signature line below. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2012 at 12:16 am
anil.janu143 (4/22/2012)
//this is my tablecreate table std_table
(
id int,
name nvarchar,
address nvarchar,
)
//this is my table data
10 null null
null anilkuar null
null null hyderabad
//i want the data like this
10 anilkumar hyderbad
If the rows are always multiples of three, this will work:
use Temp
go
create table $temp (
tempId int identity(1, 1)
,t.id int NULL
,t1.[name] nvarchar (50) NULL
,t2.[address] nvarchar (50) NULL
);
insert into @temp (id, name, address)
select * from std_table
;
select id
,name
,address
from @temp AS t
JOIN @temp AS t1
ON t1.tempId = t.tempId + 1
JOIN @temp AS t2
ON t2.tempId = t.tempId + 2
where id IS NOT NULL
;
You may have to tweak it a bit, but the idea is to insert the data into a new table with IDENTITY and do JOINs on that.
April 23, 2012 at 4:06 am
April 23, 2012 at 7:03 am
Solution is, tell whomever put the data in the table to do it again, but do it right this time. Don't split the data across multiple rows like that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2012 at 8:33 am
GSquared (4/23/2012)
Solution is, tell whomever put the data in the table to do it again, but do it right this time. Don't split the data across multiple rows like that.
+1 however i saw this same question as homework for one of my sql classes.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 23, 2012 at 8:45 am
capn.hector (4/23/2012)
GSquared (4/23/2012)
Solution is, tell whomever put the data in the table to do it again, but do it right this time. Don't split the data across multiple rows like that.+1 however i saw this same question as homework for one of my sql classes.
There are SQL classes... Therein lies the problem.
Jared
CE - Microsoft
April 23, 2012 at 9:12 am
So Jeff and I have done someone's homework...
April 23, 2012 at 10:12 am
capn.hector (4/23/2012)
+1 however i saw this same question as homework for one of my sql classes.
Interesting! Where did you take such a class? I ask because it's not exactly a BOL or CERT question and it's nice to see such classes teaching a practical thing (in this case, the pre-cursor to how CROSS TABS work).
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2012 at 10:32 am
Jeff Moden (4/23/2012)
capn.hector (4/23/2012)
+1 however i saw this same question as homework for one of my sql classes.Interesting! Where did you take such a class? I ask because it's not exactly a BOL or CERT question and it's nice to see such classes teaching a practical thing (in this case, the pre-cursor to how CROSS TABS work).
It's a good thing as long as they teach that this data "should" never exist 🙂 We don't want people to learn that "there are script fixes for bad design, so who cares about the design." I only say this because I have worked with developers who think it is my job to return the data properly, but they can store it however they want.
Jared
CE - Microsoft
April 23, 2012 at 12:21 pm
SQLKnowItAll (4/23/2012)
Jeff Moden (4/23/2012)
capn.hector (4/23/2012)
+1 however i saw this same question as homework for one of my sql classes.Interesting! Where did you take such a class? I ask because it's not exactly a BOL or CERT question and it's nice to see such classes teaching a practical thing (in this case, the pre-cursor to how CROSS TABS work).
It's a good thing as long as they teach that this data "should" never exist 🙂 We don't want people to learn that "there are script fixes for bad design, so who cares about the design." I only say this because I have worked with developers who think it is my job to return the data properly, but they can store it however they want.
it was a question during our transform data to normalize a bad design segment of a database design course. it went about like this: there is this table with bad data like so (ID,col1,col2,col2) Values (1,stuff,null,null),(1,null,stuff,null),(1,null,null,stuff),(2,Stuff,null,null)... how can you fix the data to normalize the table and get rid of the bad design?
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 28, 2012 at 10:06 am
SQLKnowItAll (4/23/2012)
Jeff Moden (4/23/2012)
capn.hector (4/23/2012)
+1 however i saw this same question as homework for one of my sql classes.Interesting! Where did you take such a class? I ask because it's not exactly a BOL or CERT question and it's nice to see such classes teaching a practical thing (in this case, the pre-cursor to how CROSS TABS work).
It's a good thing as long as they teach that this data "should" never exist 🙂 We don't want people to learn that "there are script fixes for bad design, so who cares about the design." I only say this because I have worked with developers who think it is my job to return the data properly, but they can store it however they want.
Yep... I agree with doing it right upfront but it's really nice to see someone actually trying to teach how to fix things that are bad. Too often I see courses based only on "perfect conditions" and the real world isn't quite so kind.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply