confused about pivot

I need to transform the following layout by pivoting, but am confused ......I have a compound primary key that I want to keep intact but then values in the row need to be broken out into their own row.

I need to go from this...

PKcol1 PKcol2 PKcol3 col4 col5 col6 col7

A 2007 1 Y N N N

A 2007 2 Y Y N N

A 2007 3 N N N Y

into this....

A 2007 1 col4 Y

A 2007 1 col5 N

A 2007 1 col6 N

A 2007 1 col7 N

A 2007 2 col4 Y

A 2007 2 col5 Y

A 2007 2 col6 N

A 2007 2 col7 N

A 2007 3 col4 N

A 2007 3 col5 N

A 2007 3 col6 N

A 2007 3 col7 Y

Can I do this using PIVOT or should I just do 4 inserts (one for each col40col7) into a temp table? Any suggestions?

Give a look to UNPIVOT in books online; this is an UNPIVOT and not a PIVOT

|||

Kent is right, you need to use UNPIVOT.

select

PKcol1,

PKcol2,

PKcol3,

col,

[value]

from

dbo.t1

unpivot

(

[value]

for col in ([col4], [col5], [col6], [col7])

) as unpvt;

AMB

confused about date values

I tried running this query :
select count(*) from table1 where dateadded > 1/1/2004
First of all, it worked. I didnt expect to 'cos i didnt put the quotes
around the date.
Secondly it returned a different result set than when I did include the date
select count(*) from table1 where dateadded > '1/1/2004'
CreateDate is a smalldatetime datatype.
Can someone explain this behaviour? Using SQL 2K> select count(*) from table1 where dateadded > 1/1/2004
This is equivalent to:
select count(*) from table1 where dateadded > 0
because 1/1/2004 is evaluated as an arithmetic expression without the
quotes.
The best way to specify date constants is in yyyymmdd format so that the
value is interpreted consistently regardless of the data format settings:
select count(*) from table1 where dateadded > '20040101'
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <Hassan@.hotmail.com> wrote in message
news:uKKBORXjGHA.4776@.TK2MSFTNGP05.phx.gbl...
>I tried running this query :
> select count(*) from table1 where dateadded > 1/1/2004
> First of all, it worked. I didnt expect to 'cos i didnt put the quotes
> around the date.
> Secondly it returned a different result set than when I did include the
> date
> select count(*) from table1 where dateadded > '1/1/2004'
> CreateDate is a smalldatetime datatype.
> Can someone explain this behaviour? Using SQL 2K
>|||Single quotation marks must be placed around all char, nchar, varchar,
nvarchar, text, datetime, and smalldatetime data.
As Mr. Guzman stated not following this practice in regards to a date would
cause SQL to evaluate the string as an equation.
"Hassan" wrote:

> I tried running this query :
> select count(*) from table1 where dateadded > 1/1/2004
> First of all, it worked. I didnt expect to 'cos i didnt put the quotes
> around the date.
> Secondly it returned a different result set than when I did include the da
te
> select count(*) from table1 where dateadded > '1/1/2004'
> CreateDate is a smalldatetime datatype.
> Can someone explain this behaviour? Using SQL 2K
>
>

conflict resolution

I have a merge replication setup.
I am using column level tracking
Subscribers are CLIENTS, so they are at the same level
Initially values in table t1 as
C1 -> 1
C2 -> 2
C3 -> 3
C4 -> 4
Sub1 updates,
C1->11
C2->22
Sub2 updates,
C2->222 (CONFLICT)
C3->33
1.sub1 synchronizes
2.sub2 synchronizes
3.sub1 synchronizes
My final values are,
C1->11
C2->22
C3->3
C4->4
I am expecting
C1->11
C2->22
C3->33
C4->4
Can anyone put some light?
What does the conflict viewer reveal?
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
"Ravi Lobo" <RaviLobo@.discussions.microsoft.com> wrote in message
news:33A93D07-D0DB-416D-8E7A-21D0FB06EEEB@.microsoft.com...
>I have a merge replication setup.
> I am using column level tracking
> Subscribers are CLIENTS, so they are at the same level
> Initially values in table t1 as
> C1 -> 1
> C2 -> 2
> C3 -> 3
> C4 -> 4
> Sub1 updates,
> C1->11
> C2->22
> Sub2 updates,
> C2->222 (CONFLICT)
> C3->33
> 1. sub1 synchronizes
> 2. sub2 synchronizes
> 3. sub1 synchronizes
> My final values are,
> C1->11
> C2->22
> C3->3
> C4->4
> I am expecting
> C1->11
> C2->22
> C3->33
> C4->4
> Can anyone put some light?
>