pivot information: columns

Showing posts with label columns. Show all posts

Monday, March 26, 2012

please help

Hi
I got a table with 2 columns as follows
col1 col2
10 193.51
10 194.5
10 202.71
20 192.79
20 197.6
20 192.9
30 192.76
30 191.91
30 187.9
Now i need to add a column dynamically thru sql statement to the table
so that my output should be as follows
here
0.511601468=(194.5/193.51-1)*100
4.221079692=(202.71/194.5-1)*100
and so on
col1 col2 col3
10 193.51 0.511601468
10 194.5 4.221079692
10 202.71 null
20 192.79 2.494942684
20 197.6 -2.37854251
20 192.9 null
30 192.76 -0.440962855
30 191.91 -2.08952113
30 187.9 Null
kalyan kameshkalikoi,
What criteria can we use to select the next row or the row that follows?
use northwind
go
create table t1 (
col1 int not null,
col2 numeric(9, 2)
)
go
insert into t1 values(10, 193.51)
insert into t1 values(10, 194.5)
insert into t1 values(10, 202.71)
insert into t1 values(20, 192.79)
insert into t1 values(20, 197.6)
insert into t1 values(20, 192.9)
insert into t1 values(30, 192.76)
insert into t1 values(30, 191.91)
insert into t1 values(30, 187.9)
go
alter table t1
add c1 int not null identity constraint uq_t1_c1 unique
go
select
a.col1,
a.col2,
(b.col2 / a.col2 - 1) * 100.00 as col3
from
t1 as a
left join
t1 as b
on b.c1 = (
select min(c.c1)
from t1 as c
where c.col1 = a.col1 and c.c1 > a.c1
)
order by a.c1
go
alter table t1
drop constraint uq_t1_c1
alter table t1
drop column c1
go
drop table t1
go
AMB
"kalikoi" wrote:

> Hi
>
> I got a table with 2 columns as follows
>
> col1 col2
>
> 10 193.51
> 10 194.5
> 10 202.71
>
> 20 192.79
> 20 197.6
> 20 192.9
>
> 30 192.76
> 30 191.91
> 30 187.9
>
> Now i need to add a column dynamically thru sql statement to the table
> so that my output should be as follows
>
> here
>
> 0.511601468=(194.5/193.51-1)*100
> 4.221079692=(202.71/194.5-1)*100
> and so on
>
> col1 col2 col3
>
> 10 193.51 0.511601468
> 10 194.5 4.221079692
> 10 202.71 null
>
> 20 192.79 2.494942684
> 20 197.6 -2.37854251
> 20 192.9 null
>
> 30 192.76 -0.440962855
> 30 191.91 -2.08952113
> 30 187.9 Null
>
> --
> kalyan kameshsql

Friday, March 23, 2012

please check this not null SQL String

the SQL string below worked, and then started bringing up every record.
it should only select records with a value in at least one of the columns, but it apears to be suggesting that all records have some data in one of the columns. if I check the database or the output on the web page there apears to be no data. ?? confused.

"SELECT id, make, model FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"

Any ideas how I could implement this more robustly?
cheers
MSorry, doesn't work that way.

You need a condition for each column|||Cheat. Execute:
"SELECT id, make, model
, CAST(workToBeDone1 AS VARBINARY(10)) AS w1
, CAST(workToBeDone2 AS VARBINARY(10)) AS w2
, CAST(workToBeDone3 AS VARBINARY(10)) AS w3
, CAST(workToBeDone4 AS VARBINARY(10)) AS w4
, CAST(workToBeDone5 AS VARBINARY(10)) AS w5
FROM vehicles
WHERE workToBeDone1 IS NOT NULL
OR workToBeDone2 IS NOT NULL
OR workToBeDone3 IS NOT NULL
OR workToBeDone4 IS NOT NULL
OR workToBeDone5 IS NOT NULL"If the Cast() columns do not ALL show NULL as their value, then you have data in the offending column(s). Empty strings, and sometimes even the constant "NULL" have been known to sneak into tables when you do not expect them!

-PatP|||thanks guys.
I'm sure my version was working fine until the database seemed to put something invisible into the columns.
I tried your code Pat but it returns "ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal."

What does the 'as w1' part do?

my code looks like this:
"SELECT id, make, model, CAST(workToBeDone1 AS VARBINARY(10)) AS w1, CAST(workToBeDone2 AS VARBINARY(10)) AS w2, CAST(workToBeDone3 AS VARBINARY(10)) AS w3, CAST(workToBeDone4 AS VARBINARY(10)) AS w4, CAST(workToBeDone5 AS VARBINARY(10)) AS w5 FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"|||Drop the quotes from around the SQL statement for starters ;)

For the 'As w1' try running this

SELECT id As 'Example'
FROM vehicles|||thanks georgev
sorry, I missed a crucial bit re the quotes: SQLstring="Select..."
I'll have a play with your example and see if I get it.|||nope, sorry, couldn't figure out what I am supposed to do with your example George.|||Run the thing in QA and see if you notice something.
Basically it's giving the column an alias http://doc.ddart.net/mssql/sql70/sa-ses_3.htm - scroll down to columns_alias :p|||can't use QA on this, I have to run scripts on pages on the server.
Not sure why I need aliases.
My database columns seem to contain invisible data, is there a way to discover if the columns have any meaningful data in them? NULL seems to be a bit flakey

I need to find cars that need work done - i.e. someone has inputted something like: 'replace tyres' in one of the workToBeDone fields for a Volvo. but my search is returning every car in the database because it is seeing something in the columns. (I think!).

I tried casting as varchar(255) - made no difference|||The "as W1" simply assigns an alias to the column as GeorgeV observed. It appears that your ADO implementation doesn't like the aliases.

If Query Anylyzer (or its equivalent) is available, then I'd use it instead of writing/changing code to support your ADO implementation. Operative word being "should", you should be able to simply drop the column names and move on without them.

-PatP|||And by drop the column names we don't mean physically dropping the columns... Just remove the "As ..." from your SQL statement.

The reason the aliases were applied in the first place because as soon as you perform any function on a column it loses the reference to the column name (because it's not the same as the column data any more!). The Aliases allow us to access the columns by referenec in ADO (or so I believe).|||I dropped the aliases, but it made no difference, I'm still getting:
'Item cannot be found in the collection corresponding to the requested name or ordinal',|||Ok, let's try to solve the problem from a different vector and execute:"SELECT id, make, model
, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END
, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END
FROM vehicles
WHERE workToBeDone1 IS NOT NULL
OR workToBeDone2 IS NOT NULL
OR workToBeDone3 IS NOT NULL
OR workToBeDone4 IS NOT NULL
OR workToBeDone5 IS NOT NULL"-PatP|||Thanks Pat,
still getting the same error. here's more of the code (inc. your bit) to give you a bigger picture:

Set linkRS = Server.CreateObject("ADODB.Recordset")

salePrice = request.Form("salePrice")
make=request.Form("make")
model2show=request.Form("model2show")
salePrice=request.Form("salePrice")
fuel=request.Form("fuel")
sold=request.Form("sold")
workOutstanding=request.Form("workOutstanding")
notOnWebsite=request.Form("notOnWebsite")

strSQL="SELECT id, make, model, model2show, registration, price FROM vehicles WHERE price BETWEEN "& salePrice &""
if make <> "" then strSQL = strSQL & " AND make = '" & make & "'"

if fuel <> "" then strSQL = strSQL & " AND fuel = '" & fuel & "'"

if model2show <> "" then strSQL = strSQL & " AND model2show = '" & model2show & "'"

if sold = "yes" then strSQL = strSQL & " AND sold = 'yes'"

if workOutstanding = "yes" then strSQL = "SELECT id, make, model, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL"

if notOnWebsite = "yes" then strSQL = strSQL & " AND active = 'no'"

strSQL = strSQL & " ORDER BY make"
'response.Write(strSQL)
linkRS.Open strSQL, oConn, 2, 3
if (linkRS.BOF and linkRS.EOF) then
response.Write("<p class=""inputRed"">No vehicles to display - try selecting fewer parameters</p>")
else
linkRS.moveFirst
Do while not linkRS.eof
make = linkRS("make")
'etc.
'etc.

most of this works fine, but the error message is odd because those fields do exist.|||Uncomment your 'response.Write(strSQL) and post the result.
First glance suggests you have a problem with your BETWEEN statement|||here you go:
SELECT id, make, model, CASE WHEN workToBeDone1 IS NULL THEN 0 WHEN 0 = Len(workToBeDone1) THEN 1 ELSE 2 END, CASE WHEN workToBeDone2 IS NULL THEN 0 WHEN 0 = Len(workToBeDone2) THEN 1 ELSE 2 END, CASE WHEN workToBeDone3 IS NULL THEN 0 WHEN 0 = Len(workToBeDone3) THEN 1 ELSE 2 END, CASE WHEN workToBeDone4 IS NULL THEN 0 WHEN 0 = Len(workToBeDone4) THEN 1 ELSE 2 END, CASE WHEN workToBeDone5 IS NULL THEN 0 WHEN 0 = Len(workToBeDone5) THEN 1 ELSE 2 END FROM vehicles WHERE workToBeDone1 IS NOT NULL OR workToBeDone2 IS NOT NULL OR workToBeDone3 IS NOT NULL OR workToBeDone4 IS NOT NULL OR workToBeDone5 IS NOT NULL ORDER BY make|||Maybe it contain spaces, try this
where coalesce(workToBeDone1,workToBeDone2,workToBeDone3 ,workToBeDone4,workToBeDone5,'') != ''|||thanks,
same error msg tho'

Monday, March 12, 2012

Place results in Colmn rather than rows

I have a few tables that i need to run a query on and instead of having them appear in multiple rows how do i return teh results in columns instead.

eg: System Name

1 Mr A

1 Mr B

2 Mr C

2 Mr D

INTO System Name1 Name2

1 Mr A Mr B

2 Mr C Mr D

SELECT CASE WHEN THEN ELSE END

Adamus

|||

SELECT CASE Name

WHEN System_ID = '1',

THEN

Name2

ELSE

Name3

END

Not sure i get you?

|||declare @.table table
(
[System] int,
[Name] varchar(5)
)

insert into @.table
select 1, 'Mr A' union all
select 1, 'Mr B' union all
select 2, 'Mr C' union all
select 2, 'Mr D'

select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])
from @.table a
group by a.[System]
|||

Thanks

The names Mr A, Mr B etc will be in the hundreds so don't really fancy typing them all out. There could be up to 4 or 5 different names per system.

i have tried to adapt to this but doesn;t work:-

declare @.table table

(

[System] int,

[Name] varchar(5)

)

insert into @.table

select System_ID, (firstname + ' ' + surname) as Name union all

select System_ID, (firstname + ' ' + surname) as [Name 1] union all

select System_ID, (firstname + ' ' + surname) as [Name 2]

where system = 1

From ((((dbo.System as S..........followed by my joins....

Select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])

from @.table a

group by a.[System]

How do i do this when i need to search for the criterea?

|||the table variable is for demonstrating the script.

use the query and change to your actual table name.

select a.[System], [Name 1] = min(a.[Name]), [Name 2]= max(a.[Name])
from @.table a
group by a.[System]|||

ok . got it working partially,

The Min and Max just returns 2 results? Some have 3 or 4 names?

|||do this in your front end application. It can be done in T-SQL but it will not be clean

Wednesday, March 7, 2012

PK columns dont show up in INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

Having a database user 'myuser' beeing a member of the roles 'public'
and 'db_owner' I created the test table:
if exists (select * from dbo.sysobjects where id =
object_id(N'[myuser].[TEST]') and OBJECTPROPERTY(id, N'IsUserTable')
=
1)
drop table [myuser].[TEST]
GO
CREATE TABLE [myuser].[TEST] (
[TEST_ID] [varchar] (2) NOT NULL ,
[DESCRIPTION] [varchar] (60) NOT NULL ,
CONSTRAINT [TEST_PK] PRIMARY KEY CLUSTERED
(
[TEST_ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO
However, the primary key constraint 'TEST_PK' does not show up in the
view
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
only foreign keys (from other tables) show up.
Is this a security issue?
Using SQL Server 2000 Dev Edition SP3a on Win XP Prof.
Thank you in advance for your assistance,
SRSoenke,
This happens when a user tries to get schema information from tables
that they don't own. If you login as myuser it works fine. Can you
create the table as dbo.[TEST]? If you do this then it should work
without issue.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Soenke Richardsen wrote:
> Having a database user 'myuser' beeing a member of the roles 'public'
> and 'db_owner' I created the test table:
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[myuser].[TEST]') and OBJECTPROPERTY(id, N'IsUserTable
') =
> 1)
> drop table [myuser].[TEST]
> GO
> CREATE TABLE [myuser].[TEST] (
> [TEST_ID] [varchar] (2) NOT NULL ,
> [DESCRIPTION] [varchar] (60) NOT NULL ,
> CONSTRAINT [TEST_PK] PRIMARY KEY CLUSTERED
> (
> [TEST_ID]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> However, the primary key constraint 'TEST_PK' does not show up in the
> view
> select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
> only foreign keys (from other tables) show up.
> Is this a security issue?
> Using SQL Server 2000 Dev Edition SP3a on Win XP Prof.
> Thank you in advance for your assistance,
> SR|||Hi Mark,
during the last week I tried several times to reply to your message
using google groups, but always got a message like:
"Unable to retrieve message OQ0Dm2T$EHA.3180@.TK2MSFTNGP10.phx.gbl"
Now I found the new beta groups, and they seem to work better...
Anyway, your posting helped me, thanks!
Soenke
Mark Allison wrote:[vbcol=seagreen]
> Soenke,
> This happens when a user tries to get schema information from tables
> that they don't own. If you login as myuser it works fine. Can you
> create the table as dbo.[TEST]? If you do this then it should work
> without issue.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> Soenke Richardsen wrote:
'public'[vbcol=seagreen]
N'IsUserTable') =[vbcol=seagreen]
the[vbcol=seagreen]

PK columns

How to get the list of columns which compose the PK on a table? I want to get it from system tables.
Thanks.select c.name
from
sysindexkeys k join syscolumns c
on k.id = c.id
and k.colid = c.colid
where
k.id = object_id('MyTableName')
and k.indid=1
order by k.keyno

or, simplier:

select col_name(id,colid)
from sysindexkeys
where id = object_id('A3') and indid=1 order by keyno|||Thanks. But I need to get the Primary key columns not clustered index keys. I think for indid = 1 means cluatered key, but it may not be the primary key.|||If @.tblname is null it gives the information about all the tables. If not, set it to a specific table.

declare @.tblname varchar(100)
set @.tblname = NULL
SELECT TOP 100 PERCENT WITH TIES
tc.TABLE_NAME
, kcu.COLUMN_NAME
, kcu.ORDINAL_POSITION -- Position in the key
, c.DATA_TYPE
, c.CHARACTER_MAXIMUM_LENGTH
, c.CHARACTER_SET_NAME -- typically iso_1 or Unicode
, c.COLLATION_NAME -- Case/Accent Sensitivity etc.
, c.NUMERIC_PRECISION -- Digits of data
, c.NUMERIC_SCALE -- places to right of decimal
, c.DATETIME_PRECISION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON tc.TABLE_CATALOG = kcu.TABLE_CATALOG
AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA
AND tc.TABLE_NAME = kcu.TABLE_NAME
AND tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.[COLUMNS] c
ON tc.TABLE_CATALOG = c.TABLE_CATALOG
AND tc.TABLE_SCHEMA = c.TABLE_SCHEMA
AND tc.TABLE_NAME = c.TABLE_NAME
AND kcu.COLUMN_NAME= c.COLUMN_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND (@.tblname is NULL
OR tc.TABLE_NAME = @.tblname)
AND tc.TABLE_NAME != 'dtproperties'
ORDER BY tc.TABLE_NAME
, kcu.ORDINAL_POSITION
GO

PK And Index

I have a primary key that comprises 2 columns (lets say ReportDate and
Symbol).

I know that if I submit a statement like SELECT * FROM T1 WHERE
ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.

But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?

Do I need to create another index on symbol alone?Jason (JayCallas@.hotmail.com) writes:
> I have a primary key that comprises 2 columns (lets say ReportDate and
> Symbol).
> I know that if I submit a statement like SELECT * FROM T1 WHERE
> ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.
> But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?
> Do I need to create another index on symbol alone?

For best performance, yes.

But the query may use the existing index, if the index is non-clustered.
If SQL Server finds that XYZ is not a very common value, it may opt
scan the index to find the rows. This is faster than scanning the entire
table. If the value is common, however, the bookmark lookups will be
more expensive than scanning.

If the existing index is clustered, it can not help to speed up the
retrieval. Ah, that wasn't completely true, either. Because if the
there is a non-clustered index on the table as well, the keys of the
clustered index appears in the non-clustered index, so SQL Server can
scan that index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To add to Erlands response. You could check the Execution Plan when using
Query Analyzer to see how SQL Server is using your indexes.

"Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0312190912.1c1ea341@.posting.google.c om...
> I have a primary key that comprises 2 columns (lets say ReportDate and
> Symbol).
> I know that if I submit a statement like SELECT * FROM T1 WHERE
> ReportDate = '20031219' AND Symbol = 'XYZ' it will use the index.
> But how about the statement SELECT * FROM T1 WHERE Symbol = 'XYZ'?
> Do I need to create another index on symbol alone?

PK

Are timestamp columns good candidates for primary keys? Why?Absolutely not; a timestamp will be automatically updated whenever any
column in the row is updated. That's not a good characteristic for a PK.
Non-key attributes should be dependent on the key, not the other way around.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Are timestamp columns good candidates for primary keys? Why?
>
>|||Just to follow up on that one, and no offence to Adam, a Primary Key is some
combination of values in a row that uniquely identifies a row in a table
throughout its lifetime. Hence a timestamp, which Adam pointed out is not a
good candidate as it is modified every time an update (or the initial
insert) happens. In fact I'm not even sure whether SQL would allow you to
even attempt to do that. I guess it shouldn't. But that's just my opinion.
Cheers,
Jan
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uUzYnTH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Absolutely not; a timestamp will be automatically updated whenever any
> column in the row is updated. That's not a good characteristic for a PK.
> Non-key attributes should be dependent on the key, not the other way
> around.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||Hi,
I found MVP Adam Machanic's answer is very accurate. I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

PK

Are timestamp columns good candidates for primary keys? Why?
Absolutely not; a timestamp will be automatically updated whenever any
column in the row is updated. That's not a good characteristic for a PK.
Non-key attributes should be dependent on the key, not the other way around.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"docsql" <docsql@.noemail.nospam> wrote in message
news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Are timestamp columns good candidates for primary keys? Why?
>
>
|||Just to follow up on that one, and no offence to Adam, a Primary Key is some
combination of values in a row that uniquely identifies a row in a table
throughout its lifetime. Hence a timestamp, which Adam pointed out is not a
good candidate as it is modified every time an update (or the initial
insert) happens. In fact I'm not even sure whether SQL would allow you to
even attempt to do that. I guess it shouldn't. But that's just my opinion.
Cheers,
Jan
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uUzYnTH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Absolutely not; a timestamp will be automatically updated whenever any
> column in the row is updated. That's not a good characteristic for a PK.
> Non-key attributes should be dependent on the key, not the other way
> around.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
>
|||Hi,
I found MVP Adam Machanic's answer is very accurate. I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

PK

Are timestamp columns good candidates for primary keys? Why?Absolutely not; a timestamp will be automatically updated whenever any
column in the row is updated. That's not a good characteristic for a PK.
Non-key attributes should be dependent on the key, not the other way around.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Are timestamp columns good candidates for primary keys? Why?
>
>|||Just to follow up on that one, and no offence to Adam, a Primary Key is some
combination of values in a row that uniquely identifies a row in a table
throughout its lifetime. Hence a timestamp, which Adam pointed out is not a
good candidate as it is modified every time an update (or the initial
insert) happens. In fact I'm not even sure whether SQL would allow you to
even attempt to do that. I guess it shouldn't. But that's just my opinion.
Cheers,
Jan
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uUzYnTH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Absolutely not; a timestamp will be automatically updated whenever any
> column in the row is updated. That's not a good characteristic for a PK.
> Non-key attributes should be dependent on the key, not the other way
> around.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:OIDsnQH7FHA.1028@.TK2MSFTNGP11.phx.gbl...
>> Are timestamp columns good candidates for primary keys? Why?
>>
>>
>|||Hi,
I found MVP Adam Machanic's answer is very accurate. I wanted to post a
quick note to see if you would like additional assistance or information
regarding this particular issue. We appreciate your patience and look
forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

PIVOT/CROSS TAB/Converting Rows to (multiple group) Columns

Hello All,

I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.

Consider that we have Product Category, Product and its monthly sales information retrieved as follows:

CategoryID ProductID ProductName Month UnitPrice QtySold SalesAmount 1 1 Panel Jan 5 10 50 1 1 Panel Feb 5 15 75 1 1 Panel Mar 5 20 100 1 2 Frame Jan 10 30 300 1 2 Frame Feb 10 25 250 1 2 Frame Mar 10 20 200 1 3 Glass Jan 20 10 200 1 3 Glass Feb 20 20 400 1 3 Glass Mar 20 30 600

I would like it to be converted into following result set:

CategoryID ProductID ProductName UnitPrice QtySold_Jan SalesAmt_Jan QtySold_Feb SalesAmt_Feb QtySold_Mar SalesAmt_Mar 1 1 Panel 5 10 50 15 75 20 100 1 2 Frame 10 30 300 25 250 20 200 1 3 Glass 20 10 200 20 400 30 600

I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.

Thanks.

What you are attempting to do is BEST done with the client application. SQL Server excels at storing and retreiving data. These kinds of 'transformations', while possible, are not the best use of a very expensive resource.

However, if you must, these articles demonstrate several variations of how to accomplish your goal -and they offer 'concrete' examples

Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/

PIVOT with dynamic columns names created

I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic.

For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) :

SELECT
Division,
[2] AS CurrentPeriod,
[1] AS PreviousPeriod
FROM
(
SELECT
Period,
Division,
Sales_Amount
FROM
Sales.SalesOrderHeader
WHERE
(
Period = @.period
OR Period = @.period - 1
)
) p
PIVOT
(
SUM (Sales_Amount)
FOR Period IN ( [2], [1] )
) AS pvt

Let's assume that any value 2 is selected for the @.period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1).

Division CurrentPeriod PreviousPeriod
A 400 3000
B 400 100
C 470 300
D 800 2500
E 1000 1900

What if the value @.period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ?

Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @.period parameter.

[2] AS CurrentPeriod,
[1] AS PreviousPeriod

FOR Period IN ( [2], [1] )

I have tried to use the @.period but it doesn't work.

Thanks in advance.

Kenny

This is a one drawback to the current Pivot feature. You will have to use dynamic sql for this.

Itzik has written a good article on this.

http://www.sqlmag.com/Article/ArticleID/94268/sql_server_94268.html

Saturday, February 25, 2012

Pivot transform not putting all values on same row

I have a pivot transform that it believe is configured correctly but is not distributing the values accross the columns on the same row. for example.

input:

id seqno codevalue

1 A red

1 B red

2 C blue

2 A green

2 B violet

3 A green

desired output:

id Seq_A Seq_B Seq_C

1 red red null

2 green violet blue

3 green null null

what I am getting:

id Seq_A Seq_B Seq_C

1 red null null

1 null red null

2 green null null

2 null violet null

2 null null blue

3 green null null

I do have the pivot usage for the id column set to 1. I have the pivot usage for seqno column set to 2 and codevalue column set to 3. I have the source column for each of the output columns set to the lineageID of the apprpriate input columns. I have the pivotKey values set for each of the destination columns. A for column Seq_A, B for column Seq_B, C for column Seq_C. All four columns have sortkey positions set; 1 for id, 2 for Seq_A, 3 for Seq_B and 4 for column SEQ_C.

It seems like the id column's pivot usage is not set to 1 like it should but when I check it is 1.

I also have several other pivot transforms in the same data flow and they are working as expected.

I have a suspicion that there is some hidden meta data that is messed up that is over-ridding my settings (just my guess) I have deleted this transform and re-done it several times, checking each configuration value, but still getting the same result.

Need some help or thoughts on making this work.

Thanks

Do you need to trim seqno before using it in a pivot? Might there be trailing white spaces or anything?|||

The input source has the column trimmed to char(1); I have done a dataviewer on the input and the output, and the input looks good. I have also recently (yesterday) installed SP2.

In my package, the pivot transform is after a union all transform. However, I have checked the output of the union all with a data viewer and the data input to the pivot transform looks good.

Earlier in my development of this dataflow, I did have some problems with the data source for this pivot transform, but I fixed it. And then checked the output of the pivot and found that it was not right. That is when I deleted the pivot transform and re-created it. But I still had the problem. I have since re-checked every configuration value in the pivot transform and the upstream and downstream transforms. All look to be configured correctly, but still the Pivot is putting each value on it's own row and not across the same row, for a given id.

Pivot Task Error - Duplicate pivot key

I am using the pivot task to to a pivot of YTD-Values and after that I use derived columns to calculate month values and do a unpivot then.

All worked fine, but now I get this error message:

[ytd_pivot [123]] Error: Duplicate pivot key value "6".

The settings in the advanced editor seem to be correct (no duplicate pivot key value) and I am extracting the data from the source sorted by month.

Could it be a problem that I use all pivot columns (month 1 to 12) in the derived colum transformation and they aren′t available at this moment while data extracting is still going on?

any hints?

Cheers
Markus

The pivot transform takes values like:

cust# Product Qty

-- -

1 Ham 4

1 Chips 2

1 Flan 1

2 Chips 3

2 Beer 19

and produces rows like:

cust# HamQty ChipsQty FlanQty BeerQty

-- - - - -

1 4 2 1 null

2 null 3 null 19

so what to do with input data like this?

cust# Product Qty

-- -

1 Ham 4

1 Chips 2

1 Chips 5

Which value should go into the ChipsQty column 2 or 5?

Most application would want 7, and so we suggest that the pivot be preceded by an aggregate transform to ensure that there is only 1 row for each distinct value of the pivot key. If not, you will see the error you report.

hope this helps

Pivot table with no numeric aggregation?

I'm trying to pivot the data in a table but not aggregate numeric data, just rearrange the data in columns as opposed to rows.

For example, my initial table could be represented by this structure:

Label

Value

val1

val2

val3

val4

val5

val6

val7

val8

val9

val10

and I am trying to get it into the following structure:

Label a

Label b

Label c

Label d

Label e

val1

val2

val3

val4

val5

val6

val7

val8

val9

val10

The number of labels is known beforehand, so we know how many columns to make. I can get a first step at pivoting it with 'case' statements, but obviously still end up with a row for each Label/Value pair since there is no aggregate function being applied. So I'm not sure how to get it flattened down like shown above?

Thanks for any replies on this,

Eric

In SQL Server 2005, you can use the PIVOT operator like:

select pt.ID, pt. Angel as [Label a], pt. Beer as [Label b]...

from tbl as t

pivot (min(t.value) for t.Label in ( Angel , Beer , Coffee ....)) as pt

In older versions, you can do below:

select t.ID

, min(case t.Label when 'a' then value end) as [Label a]

, min(case t.Label when 'b' then value end) as [Label b]

...

from tbl as t

group by t.ID

|||Thanks, works perfect! I had no idea you could use the min function on varchar.

PIVOT TABLE query !! @SNMSDN

hi ,

is it possible to do a pivot , where the number of columns is dynamic...i.e

i dont know how many rows will be selected , and i want to pivot them and insert into

a new (temp/tabletype)table...obv i dont know how many columns i need....

somethin like the example of books online pasted below , consider here that i need data for

all employees (distinct empid) , then pivot it, for that i'll need 'select distinct empid

from emp' in the pivot syntax 'FOR EmployeeID IN' .

pls tell me if such thing is possible or there is a turnaround for my problem...

SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID

you may try this (not sure for the perfs) :

declare @.sql1 as varchar(2000), @.sql2 as varchar(2000), @.sql3 as varchar(2000), @.empid as int

SET @.sql1 = 'SELECT VendorID '

SET @.sql2 = 'FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p '
SET @.sql2 = @.sql2 + 'PIVOT (COUNT (PurchaseOrderID) FOR EmployeeID IN ( '

SET @.sql3 = ') ) AS pvt ORDER BY VendorID'

DECLARE emp_cur CURSOR FAST_FORWARD FOR
SELECT DISTINCT EmployeeID FROM Purchasing.PurchaseOrderHeader

open emp_cur

fetch next from emp_cur into @.empid
while @.@.fetch_status = 0
begin
set @.sql1 = @.sql1 + ',' + cast(empid as varchar(10))
set @.sql2 = @.sql3 + cast(empid as varchar(10)) + ','
fetch next from emp_cur into @.empid
end

close emp_cur
deallocate emp_cur

set @.sql2 = LEFT(@.sql2, LEN(@.sql2)-1)

print @.sql1 + @.sql2 + @.sql3 -- For debug
exec (@.sql1 + @.sql2 + @.sql3)

|||

You have to create dynamic SQL and then execute it, the PIVOT statement does not support dynamic column lists itself.

You can get the list of columns by creating a variable and populating it like this

DECLARE @.pivotColumns nvarchar(2000), @.sql nvarchar(4000)
SET @.pivotColumns = ''
SELECT @.pivotColumns = @.pivotColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '],'
FROM (SELECT distinct EmployeeID FROM Purchasing.PurchaseOrderHeader) p
SET @.pivotColumns = LEFT(@.pivotColumns, LEN(@.pivotColumns) - 1)
SET @.sql = 'SELECT *
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

There is a very nice article describing this here

http://www.theabstractionpoint.com/dynamiccolumns.asp

|||

Hello:

Could you check out this thread to see whether you can figure out something?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=871809&SiteID=1

|||

thanks a lot buddy...actually i have a bit more tweek in my problem....i need the resultant recordset in a temp table.... as i dont know many columns will be in it , select into has to be used..now when i use it in buliding my query string and then execute it (@.sql) , later select * from temp , it ives an error... invalid object name '#temp' ...

(SELECT PurchaseOrderID, EmployeeID, VendorID
into #temp

FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

select * from #temp

-->some more help....

|||

The problem is that local temporary tables (with a # at the beginning of the name) are very local - so they are gone after the dynamic SQL finishes executing.

You could create a global temporary table (with two ## at the beginning of the name). The problem is that then the temp table will be available to all connections, so if it is possible that this code will ever run on two connections at the same time that won't work. So now you have to get tricky, you create the #temp table first (with the known VendorID column, but none of the other columns, because they are not known). Then you alter the table in the dynamic code before you insert.

DROP TABLE #temp
CREATE TABLE #temp(VendorID int)
DECLARE @.pivotColumns nvarchar(2000), @.alterColumns nvarchar(2000), @.sql nvarchar(4000)
SET @.pivotColumns = ''
SET @.alterColumns = ''
SELECT @.pivotColumns = @.pivotColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '],',
@.alterColumns = @.alterColumns + '[' + cast(EmployeeID AS nvarchar(10)) + '] int,'
FROM (SELECT distinct EmployeeID FROM Purchasing.PurchaseOrderHeader) p
SET @.pivotColumns = LEFT(@.pivotColumns, LEN(@.pivotColumns) - 1)
SET @.alterColumns = LEFT(@.alterColumns, LEN(@.alterColumns) - 1)
SET @.sql = 'ALTER TABLE #temp
ADD ' + @.alterColumns
EXEC(@.sql)
SET @.sql = 'INSERT #temp
SELECT *
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

SELECT * FROM #temp

|||thanka a lot buddy....works perfect for me..

PIVOT TABLE query !!

hi ,

is it possible to do a pivot , where the number of columns is dynamic...i.e

i dont know how many rows will be selected , and i want to pivot them and insert into

a new (temp/tabletype)table...obv i dont know how many columns i need....

somethin like the example of books online pasted below , consider here that i need data for

all employees (distinct empid) , then pivot it, for that i'll need 'select distinct empid

from emp' in the pivot syntax 'FOR EmployeeID IN' .

pls tell me if such thing is possible or there is a turnaround for my problem...

you may try this (not sure for the perfs) :

declare @.sql1 as varchar(2000), @.sql2 as varchar(2000), @.sql3 as varchar(2000), @.empid as int

SET @.sql1 = 'SELECT VendorID '

SET @.sql2 = 'FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p '
SET @.sql2 = @.sql2 + 'PIVOT (COUNT (PurchaseOrderID) FOR EmployeeID IN ( '

SET @.sql3 = ') ) AS pvt ORDER BY VendorID'

DECLARE emp_cur CURSOR FAST_FORWARD FOR
SELECT DISTINCT EmployeeID FROM Purchasing.PurchaseOrderHeader

open emp_cur

close emp_cur
deallocate emp_cur

set @.sql2 = LEFT(@.sql2, LEN(@.sql2)-1)

print @.sql1 + @.sql2 + @.sql3 -- For debug
exec (@.sql1 + @.sql2 + @.sql3)

|||

You have to create dynamic SQL and then execute it, the PIVOT statement does not support dynamic column lists itself.

You can get the list of columns by creating a variable and populating it like this

There is a very nice article describing this here

http://www.theabstractionpoint.com/dynamiccolumns.asp

|||

Hello:

Could you check out this thread to see whether you can figure out something?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=871809&SiteID=1

|||

(SELECT PurchaseOrderID, EmployeeID, VendorID
into #temp

FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

select * from #temp

-->some more help....

|||

The problem is that local temporary tables (with a # at the beginning of the name) are very local - so they are gone after the dynamic SQL finishes executing.

SELECT * FROM #temp

|||thanka a lot buddy....works perfect for me..

PIVOT TABLE query !!

hi ,

is it possible to do a pivot , where the number of columns is dynamic...i.e

i dont know how many rows will be selected , and i want to pivot them and insert into

a new (temp/tabletype)table...obv i dont know how many columns i need....

somethin like the example of books online pasted below , consider here that i need data for

all employees (distinct empid) , then pivot it, for that i'll need 'select distinct empid

from emp' in the pivot syntax 'FOR EmployeeID IN' .

pls tell me if such thing is possible or there is a turnaround for my problem...

you may try this (not sure for the perfs) :

declare @.sql1 as varchar(2000), @.sql2 as varchar(2000), @.sql3 as varchar(2000), @.empid as int

SET @.sql1 = 'SELECT VendorID '

SET @.sql2 = 'FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p '
SET @.sql2 = @.sql2 + 'PIVOT (COUNT (PurchaseOrderID) FOR EmployeeID IN ( '

SET @.sql3 = ') ) AS pvt ORDER BY VendorID'

DECLARE emp_cur CURSOR FAST_FORWARD FOR
SELECT DISTINCT EmployeeID FROM Purchasing.PurchaseOrderHeader

open emp_cur

close emp_cur
deallocate emp_cur

set @.sql2 = LEFT(@.sql2, LEN(@.sql2)-1)

print @.sql1 + @.sql2 + @.sql3 -- For debug
exec (@.sql1 + @.sql2 + @.sql3)

|||

You have to create dynamic SQL and then execute it, the PIVOT statement does not support dynamic column lists itself.

You can get the list of columns by creating a variable and populating it like this

There is a very nice article describing this here

http://www.theabstractionpoint.com/dynamiccolumns.asp

|||

Hello:

Could you check out this thread to see whether you can figure out something?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=871809&SiteID=1

|||

(SELECT PurchaseOrderID, EmployeeID, VendorID
into #temp

FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( ' + @.pivotColumns + ' )
) AS pvt
ORDER BY VendorID'
EXEC (@.sql)

select * from #temp

-->some more help....

|||

The problem is that local temporary tables (with a # at the beginning of the name) are very local - so they are gone after the dynamic SQL finishes executing.

SELECT * FROM #temp

|||thanka a lot buddy....works perfect for me..

pivot table

I'm trying to extract some data from an ssas 2005 cube to build a report with ssrs 2005.

The report should have a variable number of columns and a fixed number of rows ... so I think I cannot use a table control but I must use a matrix control ...

So I would group the column for the fiscal month and the row for the measure name or measure caption ... and put the measure value inside the matrix.

Like the following

month 1 month 2 measure 1 xxx xxx measure 2 xxx xxx measure 3 xxx xxx

To do that I should run a query to extract data in the following form ...

fiscal month mesaure name measure value month 1 measure 1 xxx month 1 measure 2 xxx month 1 measure 3 xxx month 2 measure 1 xxx month 2 measure 2 xxx month 2 measure 3 xxx

The problem is ... when running an mdx query on reporting services I need to put the meausure only on the columns ...

so any idea on how can I extract data from ssas in that form ?

Cosimo

Can you place an upper bound on the number of months you need to report? Also, how do you want your months to display from left to right -- most recent to least recent?|||

thank

I solved ... using a static group and a matrix control

Cosimo

PIVOT statement whitout knowing values

Just a small issue...

I'm trying the new SQL 2005 (Express) because the PIVOT function was finally added.

I've a table with three columns ID, Height and Width

Now I'd like to have a table with for each height the number of ID for each Width

The easiest way is to use the PIVOT statement.....but..... to use it in SQL2005 I should use:

SELECT Height, [100] AS Width01, [200] AS Width02
FROM (

SELECT ID, Height, Width FROM TestTable) p

PIVOT ( COUNT (ID) FOR Width IN([100], [200]) ) AS pvt

This kind of querry works perfectly in a static situation, but if I add new record in the table referencing the "300" Width to obtain the correct result I have to modify the query.

Is there an options or a technique for having the list of the Width dinamically filled according the table contents.

Thank you very much to anyone how can help me

You have to use dynamic SQL to execute the SELECT statement after generating the values for the IN list. There is no other way using static SQL code.|||

To be clear, there are good reasons for this restriction.

SQL Server's PIVOT can exist anywhere in the query tree (unlike in Access), supports UNPIVOT (unlike Access), and does not require recompilation for each execution (unlike Access). These are good things for complex queries, as compilation time would be significantly worse if these did not exist.

SQL Server's query optimizer has a requirement that the column list be known before compilation begins. This allows faster compiles because we can identify duplicate alternatives more easily and avoid doing extra work during compilation. This also helps us to determine if we can avoid searching portions of the possible plan space that obviously will not help find a faster plan than what has been found so far during optimization.

I understand the desire to not have to bother specifying a column list, and perhaps that is something we can add in a future release. The reasons above are reasons it was not added in SQL 2005. Even if such a feature were added, it would be likely better if you could specify a column list to speed system throughput.

Conor Cunningham

SQL Server Query Optimization Development Lead

|||

Thank you all for the clear answer, now I understood that the restriction is due to performances.

Of course this type of restriction have very few impact over small databases like the ones I working on (~100 MB). So I will keep my application over access where the power of the TRANSFORM-PIVOT scheme will help me reducing the programming effort.

Thanks again

PIVOT sql_variant into underlying dataypes

Does anyone know of a way to PIVOT an sql_variant column so that the resulting columns will be their proper underlying datatypes? I wish this to be done in a View.
I currently do this by hard coding the conversion as follows:

SELECT A, B, C
MAX(CASE Letters WHEN 'D' THEN CONVERT(int, LetterValue) ELSE Null END AS D,
MAX(CASE Letters WHEN 'E' THEN CONVERT(datetime, LetterValue) ELSE Null END AS E,
MAX(CASE Letters WHEN 'F' THEN CONVERT(varchar, LetterValue) ELSE Null END AS F
FROM Alphabet
GROUP BY A, B, C

I would like to take advantage of the SQL_VARINIANT_PROPERTY(LetterValue, 'BaseType') function so I do away with the hard coding.
Any ideas?

Jim:

Is this the transformation you are looking for:

create table dbo.alphabet
( A tinyint, -- Needs to be changed
B tinyint, -- Needs to be changed
C tinyint, -- Needs to be changed
Letters char(1),
LetterValue sql_variant
)
go
insert into dbo.alphabet values (1, 2, 3, 'D', 29)
insert into dbo.alphabet values (2, 2, 2, 'E', convert (datetime, '2/20/7'))
insert into dbo.alphabet values (3, 2, 1, 'F', 'This is a test.')
insert into dbo.alphabet values (4, 5, 6, 'F', 'Just another test.')
insert into dbo.alphabet values (5, 5, 5, 'D', 30)
go
--select * from alphabet
select A,
B,
C,
cast ( as integer) as ,
cast ( as datetime) as ,
cast ([F] as varchar) as [F]
from dbo.alphabet
pivot ( max(LetterValue)
for Letters in (,,[F])
) alphabetPivot
-- A B C D E F
-- - - - -- --
-- 1 2 3 29 NULL NULL
-- 2 2 2 NULL 2007-02-20 00:00:00.000 NULL
-- 3 2 1 NULL NULL This is a test.
-- 4 5 6 NULL NULL Just another test.
-- 5 5 5 30 NULL NULL

|||That is the idea but I do not want to hard code the CAST. Somehow I would like to use the SQL_VARIANT_PROPERTY() function to determine the datatype during the PIVOT.
I was hoping for something like
PIVOT( max(CONVERT(SQL_VARIANT_PROPERTY(LetterValue, 'BaseType'), LetterValue)))
FOR Letters in ( Drinks

, [F])
Probably not possible unless the SQL is dynamic.|||

Jim:

You are correct that SQL_VARIANT_PROPERTY will not behave as you need it for this type of abstraction.

|||Thanks for your input.|||You could probably do this with dynamic SQL, but why do you need this anyhow? The data in the sql_variant will be of the proper type, so really wouldn't only the data user be the only one that needs to be concerned with the type of the data?

Monday, March 26, 2012

please help

Friday, March 23, 2012

please check this not null SQL String

Monday, March 12, 2012

Place results in Colmn rather than rows

Wednesday, March 7, 2012

PK columns dont show up in INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

PK columns

PK And Index

PK

PK

PK

PIVOT/CROSS TAB/Converting Rows to (multiple group) Columns

PIVOT with dynamic columns names created

Saturday, February 25, 2012

Pivot transform not putting all values on same row

Pivot Task Error - Duplicate pivot key

Pivot table with no numeric aggregation?

PIVOT TABLE query !! @SNMSDN

PIVOT TABLE query !!

PIVOT TABLE query !!

pivot table

PIVOT statement whitout knowing values

PIVOT sql_variant into underlying dataypes

pivot information

Blog Archive

About Me