Regarding Data Transpose

Hello,
I have got a table, which consists 5 columns. If suppose there are ten
rows, then I want to insert the data present in this table into another
temporary table, which consists of 50 columns. So, effectively I want to
convert all the rows into one row by transposing. How can I do this?
--With Regards,
Sheshadrinath.R"Sheshadrinath R" <SheshadrinathR@.discussions.microsoft.com> wrote in
message news:37037772-7988-45E4-B957-62439844EFD8@.microsoft.com...
> Hello,
> I have got a table, which consists 5 columns. If suppose there are ten
> rows, then I want to insert the data present in this table into another
> temporary table, which consists of 50 columns. So, effectively I want to
> convert all the rows into one row by transposing. How can I do this?
> --With Regards,
> Sheshadrinath.R
Why? Have you considered just changing the way you display the data rather
than attempting such a thing in the database?
You haven't given enough information to answer your question fully. How
should we determine which rows get transposed to which columns for example?
Google this group for "transpose" and "crosstab" and you'll find plenty of
examples that might help you.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Regarding a sample query

Hello,
I am a very complex problem in front of me. Kindly help me out

in acheiving the same.

Say I have a table called InfoName with two columns Name and ID

InfoName

Name ID

OS 1
SP 2
Driver 3
fasdf **
** ***
** ****

(I AM INTERESTED IN ONLY FIRST THREE ROWS )

I have another table Infotxt which uses the ID of InfoName as

foreign key. It stores the value of this ID as shown

InFotxt

ID Value UnitNAME

1 Win 2000 raj
2 SP 4 raj
3 40 GB raj

1 Win xp jay
2 SP 2 jay
3 20 GB jay

NOw I need to present it with unitname's configuration of OS,

Sp and disk capacity like below.

name OS SP Drive
Raj win2000 sp4 40 GB
Jay winxp sp2 2o GB

That is, the rows of the InfoName table (first 3 rows) should

be the columns of my resultant query.

How can I achieve the same.
Please give me some ideas, and if the question is silly, I am

very sorry, because I am new to database queries...

Thanks,
cspek

cspek

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Try this:

SELECT unitname,
MIN(CASE WHEN id = 1 THEN value END) AS os,
MIN(CASE WHEN id = 2 THEN value END) AS sp,
MIN(CASE WHEN id = 3 THEN value END) AS drive
FROM InfoText
WHERE id BETWEEN 1 AND 3
GROUP BY unitname

You have to be more specific than "first three rows". Understand that
tables in SQL are not ordered. There is no fixed concept of a first,
second or Nth row.

This is called a cross-tab report. There are other solutions for
producing cross-tabs dynamically in SQL Server but many people would
say that you should do this instead in your client application or
reporting tool. See:

http://www.aspfaq.com/show.asp?id=2462

--
David Portas
SQL Server MVP
--|||Hello,
Thanks...Wil look into it...

cspek

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Reg: Query Performence

Below query output results time taking very much.

SELECT NON EMPTY

{[Measures].[Score]} ON COLUMNS,

NON EMPTY {

(DESCENDANTS({[Organisation].[Organisation].&[36]},0),

[Question].[Type Id].[Category Id],

[Question].[Type Description].[Category Description],

[Question].[Short Description].[Short Description]

)}

HAVING [Measures].[Points]>0 ON ROWS

FROM (SELECT [Assignment].[Id].&[1] ON COLUMNS

FROM [Sample])

Total 7 dimensions 2 measure groups

Fact Table : 18,000,00 Records

Dimensions: 2000 Records

[Organisation].[Organisation] having 9 levels

like

Level1

Level2

LEVEl3

Is there any solution to improve the query performence.Any steps to inreasing performence of cube. Please help me. this is urgent for me

Could you please rewrite your query in terms of AdventureWorks?

Referencing AS columns

I'm just converting my Access database to SQL Server and have come across a number of differences. In Access I was able to do the following:
SELECT ZNew = Max( ..
ZNew2 = [ZNew] - Price
FROM ...
Can I reference ZNew in line 2 above, or do I need to duplicate the 'Max(' line? Help would be appreciated. Thanks.Try:

SELECT ZNew = Max([ZNew] - Price)
FROM ...

blindman|||Did you mean ZNew2 = Max([ZNew] etc. ...
The first line ZNew = Max( .. is an extensive CASE evaluation.
Nice not to have to repeat it in the ZNew2 expression.|||Can I reference ZNew in line 2 above, or do I need to duplicate the 'Max

No, seems to me that you must specify all the syntax :

SELECT ZNew = Max( ...),
ZNew2 = Max(...) - Price
FROM ...|||If you are using a case function you need to show us your statement.

blindman|||Here's the code:
SELECT TCode, ZValue = Max(CASE WHEN TCode = 'AAA' AND TValue > 1000 THEN 1000
WHEN TCode = 'BBB' AND TValue > 500 THEN 500 ELSE TValue END) ,
ZNew2 = ZValue - TKgValue|||You haven't included your FROM clause, so I can't tell if ZValue exists in an underlying table as well as being constructed from your case clause. If your tables have a field called ZValue in them, that is the value that will be used when you try to calculate ZNew2. Otherwise, I think you will get an error stating that SQL Server can't find field ZValue. You cannot create it and then reference it in the same statement, so you will have to repeat your case statement.

There are ways to avoid repeating the CASE statement, such as this method using nested queries:

SELECT TCode,
ZValue,
ZValue - TKgValue ZNew2
FROM (SELECT TCode,
Max(CASE WHEN TCode = 'AAA' AND TValue > 1000 THEN 1000
WHEN TCode = 'BBB' AND TValue > 500 THEN 500
ELSE TValue END) ZValue,
TKgValue
From YourTableReferencese) ZValueSubquery

blindman|||The Value does not exist and a nested query will not work in this case, so I'll just have to repeat the CASE statement. Thanks.|||I think your code would be easier to maintain, (and may run faster) if you use the nested query approach.

blindman

Referencing a conditional column in the WHERE clause -- Possible?

I have two tables which I'm joining in a query. In the join, I'm creating a column whose value is conditional (see columns Actual_Latitude and Actual_Longitude below). Is it possible to reference the created column in the WHERE clause?

Consider the following query:

---

SELECT
S.StationID
, P.PoleID
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID

---

I'd like to be able to add the following:

---

WHERE Actual_Latitude > 50

---

...But, I'm getting an "Invalid column name" error. Is this possible in some way?

The benefit, of course, would be that I wouldn't have to repeat the conditions in the WHERE clause.

Unfortunately, you can't.

You are creating an ALIAS for an expression, and the expression is not 'known' by that ALIAS in the 'acquisition' part of the query. Once the data is acquired, you can refer to the ALIAS in the ORDER BY because a 'derived table' has been determined.

You could, however, wrap this query in another, and use the ALIAS in the outer query. That does't provide much help with filtering though...

The 'best' option is to repeat the CASE structure for Actual_Latitude in the WHERE clause.

|||

I agree with Arnie for the most part. This will work:

SELECT S.StationID , P.PoleID

--NOTE: change to this means a change to the where clause for Actual_latitude!!
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID
where CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END > 50 --Actual_Latitude > 50

In 2005, I would probably try this and see how it works out. It probably will have the same plan and is a bit clearer. If this is a highly used, performance intensive operation I would consider rewriting the query to eliminate the CASE in the where clause and express it as just expressions (it could be done, I think):

WITH stationQuery AS (

SELECT S.StationID , P.PoleID
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Latitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Latitude
ELSE NULL
END AS Actual_Latitude
, CASE WHEN (S.PoleID IS NOT NULL AND P.Latitude IS NOT NULL AND P.Longitude IS NOT NULL) THEN P.Longitude
WHEN (S.Latitude IS NOT NULL AND S.Longitude IS NOT NULL) THEN S.Longitude
ELSE NULL
END AS Actual_Longitude
FROM Stations S
LEFT JOIN Poles P on S.PoleID = P.PoleID
select *

from stationQuery

where actual_latitude > 50

The thing is, you are not going to get good performance no matter how you do it. Since the both of your tables in the join are tied up in the CASE expression, very unlikely to get any index utilization. You could also do it as a derived table in 2000.

REFERENCEing two columns to the same key

Hi

I'm just wondering whether SQL Server will let me link two columns in one table to the same column in another table. Basically, my table looks like this:

BOOKINGS
-----
BookingID (PK)
BorrowerID (FK1)
ModuleID (FK2)
LecturerID (FK3)
CollectionDateTime
ReturnDateTime
Authorised
TakenUp

FK1 and FK3 are related to BorrowerID - both lecturers and students are stored in the same table because lecturers are able to book and borrow equipment as well as students. Will SQL Server let me create a Foreign Key on BorrowerID and LecturerID that both point to Borrowers.BorrowerID?

My understanding of Foreign Keys is that they're just a way of enforcing referential integrity, so they'll only come into play when data is being deleted that might cause orphaned rows in the dependent table. So there shouldn't be too much of a problem with this, is that right?

Thanks
JonYes, absolutely. SQL Server will let you link FK1 and FK3 to the same table. However, if you want to enforce referential integrity systematically (ie cascading updates and deletes), you will only be able to do so on one of the foreign keys. With the other foreign key you will have to use a trigger. This is one of SQL's annoying "personalities" .

Terri|||Cool - thanks for your reply

You might be able to tell, but I'm quite a SQL novice. I'm guessing the trigger would be in effect on INSERTs and would check that one of the Foreign Keys is a valid value? Would it need to be effective on anything else? I guess UPDATES, but what about DELETES?

Great, now I have to learn triggers!!!

Cheers
Jon|||This KB article describes the problem you will encounter:PRB: Error Message 1785 Occurs When You Create a FOREIGN KEY Constraint That May Cause Multiple Cascade Paths

So, in your case you cannot use what is referred to as Declarative Referential Integrity (DRI).

This is an area where I am not particularly knowledgeable. I have not used DRI nor triggers for referential integrity. For better or worse, the stored procedure doing the data modification is where I have put such code.

Terri

Reference previously calculated values in table

I have a table with two columns.
Column A contains a complicated calculation. Column b contains a new calculation which requires the value from Column A. Is there a way to reference the Column A value without re-entering the formula.
Thanks for your help in advance.=ReportItems!textbox1.Value.
[Replace textbox1 with the appropriate cell name in the table.]
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:1A68EE0F-1789-4F2E-973B-61790F19D657@.microsoft.com...
> I have a table with two columns.
> Column A contains a complicated calculation. Column b contains a new
calculation which requires the value from Column A. Is there a way to
reference the Column A value without re-entering the formula.
> Thanks for your help in advance.

Redundancy requiring Identity columns on Subscriber - please help

Hi
I am just getting up to speed on replication so please forgive me if I
am missing something simple here......
Scenario
Application server (call it Test) that has SQL Server 2000 database
I am setting up replication for backup pupopses
(If Server A dies - point Application (Test) to Server B )
My Problem -
Many tables in the database have the identiy column set to YES
As the TEST operates, many rows uniquely added to the db using Identity
column as PKeys
I was using snapshot replication for test purposes....(no license for
Transactional)
On subscriber - created blank DB with YES (Not for Replication) option
set for the tables in question.
The Snapshot I created from Publisher, when I push to the subscriber -
it resets the Identify (YES - NFR) back to No
I redireecte dteh Aopplication server to Server B an dit fails to work
corrcectly.- cannot insert into row s where Identity column is set to
No
Identify needs to be set to YES (or YES not for Repl) in order for my
application server to work correctly.
Any ideas on how I could best achieve this?
Thanks in advance........
Kevha
right click on your publication, select properties, in the articles tab
select the browse button to the right of your tables. Click on the snapshot
tab. In the name conflicts section, select the keep delete existing data.
Now return to your subscriber, fix things the way you want them, and then
rereun your snapshot.
It should work this time.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<kevha@.hotmail.com> wrote in message
news:1132178886.227006.217160@.o13g2000cwo.googlegr oups.com...
> Hi
> I am just getting up to speed on replication so please forgive me if I
> am missing something simple here......
> Scenario
> Application server (call it Test) that has SQL Server 2000 database
> I am setting up replication for backup pupopses
> (If Server A dies - point Application (Test) to Server B )
> My Problem -
> Many tables in the database have the identiy column set to YES
> As the TEST operates, many rows uniquely added to the db using Identity
> column as PKeys
> I was using snapshot replication for test purposes....(no license for
> Transactional)
> On subscriber - created blank DB with YES (Not for Replication) option
> set for the tables in question.
> The Snapshot I created from Publisher, when I push to the subscriber -
> it resets the Identify (YES - NFR) back to No
> I redireecte dteh Aopplication server to Server B an dit fails to work
> corrcectly.- cannot insert into row s where Identity column is set to
> No
> Identify needs to be set to YES (or YES not for Repl) in order for my
> application server to work correctly.
> Any ideas on how I could best achieve this?
> Thanks in advance........
> Kevha
>
|||Hi Hilary
I still can't seem to get the problem solved.
Let me take it back a step -
Server A (source)
Server B (subscriber)
I want to replicate server A to server B
I set up Server A with Snapshot Replicationa sPublisher and
Distributor.
Database created on Server A - some tables with Columns set with
Identity = Yes
I set up Snapshot Replication
In the SnapShot Properties/ Articles I set 'Keep Existing Table
unchanged' option on.
I added a custom script in the Snapshot Properties tab that - Drops the
tables, recreates them with the IDENTITY NOT FOR REPLICATION option
included during table creation.
When I conduct the Synchronize - I get the following error -
Violation of Primary Key constraint 'x' insert duplicate key in object
'x'
My replication could happen once an hour (not real time critical so any
option that keeps the Identity filed intact appreciated)