Help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help with query

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2005-03-10 : 14:46:23

can someone help me write a query...
I have 2 tables with the following structure:
CREATE TABLE [dbo].[TABLE1] (
[Cmy] [varchar] (50) NULL ,
[Nm] [varchar] (50) NULL ,
[Num] [varchar] (20) NULL ,
[Phn] [varchar] (20) NULL

Data for table1:
Cmy Nm Num Phn
BMW BMW Inc. 123456789 180008888
Dodge Dodge Inc. 789789789 1888456789

CREATE TABLE [dbo].[Table2] (
[FieldKey] [varchar] (50) NOT NULL ,
[FieldText] [varchar] (100) NOT NULL
) ON [PRIMARY]

Data for table2:
FieldKey FieldText
ICompany Company
IName Company Full Name
INumber Company Number
IPhone Company Phone

Since the fields are in table2 doesn't nessesary reflect the actual name in table1 I was able to
get the string for the fields I need to select:
Declare @SQLString as nvarchar(4000)
Set @SQLString=''
Select @SQLString=@SQLString +
Case FieldKey

when 'ICompany' then 'Cmy'
when 'IName' then 'NM'
when 'PolicyNum' then 'InsuranceNumber'
when 'INumber' then 'Num'
when 'IPhone' then 'Phn'
END + ','
From table2
EXEC sp_executesql @SQLString...
that is where I stop and have no clue what to do next. I need to display data like this:
Company: BMW
Company Full Name: BMW Inc.
Company Number: 123456789
Company Phone: 180008888

The problem is for a different company I will have diffrent column selected. Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-10 : 15:47:19

I can't imagine how this format will be usefull. Can't you just use some mailing address software?
anyway, here ya go:


CREATE TABLE [dbo].[TABLE1] (
[Cmy] [varchar] (50) NULL ,
[Nm] [varchar] (50) NULL ,
[Num] [varchar] (20) NULL ,
[Phn] [varchar] (20) NULL )
GO
insert table1
Select	'BMW', 'BMW Inc.', '123456789', '180008888' union all
Select	'Dodge', 'Dodge Inc.',  '789789789', '1888456789'
GO
--Add a rowID to Table1
alter table table1 add RowID int identity(1,1) --for sorting results
GO
Select	Case 
                --transpose columns to rows
		when ColNo = 0 then 'Company: ' + cmy
		when ColNo = 1 then 'Company Full Name: ' + nm
		when ColNo = 2 then 'Company Number: ' + num
		when ColNo = 3 then 'Company Phone: ' + phn
	End
from	table1 tb1
cross join
        --derived table with as many 0 based records as columns in table1
	(Select t1.t + t2.t colNo
	From	(Select 0 t union select 1) t1,
		(Select 0 t union select 2) t2
	) as colcount
Order by 
	Rowid,ColNo

Edit: I added the GOs so the statement could run in one fell swoop

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-10 : 15:56:20

quote:

Originally posted by TG

I can't imagine how this format will be usefull. Can't you just use some mailing address software?
anyway, here ya go:


CREATE TABLE [dbo].[TABLE1] (
[Cmy] [varchar] (50) NULL ,
[Nm] [varchar] (50) NULL ,
[Num] [varchar] (20) NULL ,
[Phn] [varchar] (20) NULL )

insert table1
Select	'BMW', 'BMW Inc.', '123456789', '180008888' union all
Select	'Dodge', 'Dodge Inc.',  '789789789', '1888456789'

--Add a rowID to Table1
alter table table1 add RowID int identity(1,1) --for sorting results

Select	Case 
                --transpose columns to rows
		when ColNo = 0 then 'Company: ' + cmy
		when ColNo = 1 then 'Company Full Name: ' + nm
		when ColNo = 2 then 'Company Number: ' + num
		when ColNo = 3 then 'Company Phone: ' + phn	End
from	table1 tb1
cross join
        --derived table with as many 0 based records as columns in table1
	(Select t1.t + t2.t colNo
	From	(Select 0 t union select 1) t1,
		(Select 0 t union select 2) t2
	) as colcount
Order by 
	Rowid,ColNo

Be One with the Optimizer
TG

TG, Your code yields the following result

!

Server: Msg 207, Level 16, State 3, Line 14
Invalid column name 'Rowid'.

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-10 : 15:57:46

did you run the ALTER table statment? (above the select)

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-10 : 16:01:59

quote:
Originally posted by TG

did you run the ALTER table statment? (above the select)

Be One with the Optimizer
TG

I did a cut-n-paste into SQA

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-10 : 16:05:22

I just re-cut and pasted and it runs fine here. I don't know what to tell, big man.

Does Table1 have the RowID column when you do "sp_help Table1"? If not re-run the alter statement.

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-10 : 16:06:43

you need to put GO after alter table.
~~alter table changes don't apply in the same batch.~~

EDIT:
well it seems you can do

select * from table1

and it returns results with the identity
but you can't do

select * from table1
order by RowId

it gives that error.
At least for me.

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-10 : 16:24:20

GO makes the difference.

I personally was interested in this particular topic due to the TRANSPOSE idea. In SAS we can do PROC TRANSPOSE without all this hassle. SAS spoiled me

, so I have to learn the hard way to do some things.

Thanks!

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-10 : 16:25:52

Ok you guys, if I have to spell it out. Either put GOs around the Alter Table statement or execute the commands seperately so that you end up with Table1 having a RowID column. Then the statement should run fine.

Sheesh!

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-10 : 16:28:03

hehe TG...
then explaing why you can do select but not a select with order by without GO?

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-10 : 16:31:57

Ouuoo, Ouuuoo!! I know! I know!

When the plan is being created, the Alter statement isn't taken into account so the optimizer doesn't know the column is there and throws the error before actual execution.

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-10 : 16:33:28

ROTFL!
cool. i acctualy didn't know that one... shame on me.

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-10 : 16:35:35

quote:
Originally posted by spirit1

hehe TG...
then explaing why you can do select but not a select with order by without GO?

Go with the flow & have fun! Else fight the flow

This IS WEIRD!

I went BOL-ing (get it?) and I find that GO SIGNALS the end of a T-SQL statement, BUT it's NOT a T-SQL statement. HUH?

Also, a T-SQL statement CANNOT occupy the same line as the GO command.

How weird is that?

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-10 : 16:39:53

GO is a scope terminator

I will be bock...dopplebock

Brett

8-)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-10 : 16:41:00

I edited my orginal post to add the GOs.

Xerxes, keep reading! BOL is a real page-turner! Definately rivals the Tolkien trilogy.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-10 : 16:42:27

Next thing ya know, GO will be running for governor somewhere.

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-10 : 16:42:48

ok i read the trilogy + hobbit 7 times and silmarilion 2 times so don't you go
saying BOL is a better page turner

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-10 : 16:43:40

Only 7 times?? You must be a young. :)

edit:
that sounds bad out of context

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-10 : 16:45:03

But in all the BOL-ing I do, I still haven't cracked 300.

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-10 : 16:45:42

only 25... started reading it eight time but got bored and stopped. needed a break.
i'm reading master and margharet by Bulgakov at the moment. cool book! a classic. that is when i'm not reading neural cybernetics which is the exam i have on moday

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-10 : 17:44:27

Let's set this topic on FIRE!!

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources