Create a view based on the exiting table ?

Question

Hi guys,

i am stuck on a one issues these days, like i have a table like bellow

Id     DocId    FieldName              FieldValue
1       201       LastPrintPerson      Dominic lorenzo
2       201       LastPrintDate         01/02/2007
3       204       LastPrintPerson      Gomez Hwang
4       204       LastPrintDate         01/07/2008


Now what i need to do is i need to create a view based on that above table structure like bellow,

DocId    LastPrintPerson        LastPrintDate
201       Dominic lorenzo       01/02/2007
204       Gomez Hwang          01/07/2008



any idea to do this ?

thanks
regards
sukavi

Answer 1

Hi,

Try this:

with cte as (
     select 1 as ID, 201 as DocID, 'LastPrintPerson' as FieldName, 'Dominic lorenzo' as FieldValue
     union all
     select 2 as ID, 201 as DocID, 'LastPrintDate' as FieldName, '01/02/2007' as FieldValue
     union all
     select 3 as ID, 204 as DocID, 'LastPrintPerson' as FieldName, 'Gomez Hwang' as FieldValue
     union all
     select 4 as ID, 204 as DocID, 'LastPrintDate' as FieldName, '01/07/2008' as FieldValue
)
select
     c1.DocID
,    c1.FieldValue as LastPrintPerson
,    c2.FieldValue as LastPrintDate
from cte c1
join cte c2 on
   c1.DocID = c2.DocID  
where
     c1.FieldName = 'LastPrintPerson'
     and c2.FieldName = 'LastPrintDate'

Regards,
John

Answer 2

Here is another way

DECLARE @t TABLE(id INT IDENTITY(1,1),DocID INT,FieldName VARCHAR(MAX),FieldValue VARCHAR(MAX))

INSERT INTO @t (DocID,FieldName,Fieldvalue)
SELECT 201,'LastPrintPerson','Dominic lorenzo' UNION ALL
SELECT 201,'LastPrintDate','01/02/2007' UNION ALL
SELECT 204 ,'LastPrintPerson','Gomez Hwang' UNION ALL
SELECT 204,'LastPrintDate','01/07/2008'

SELECT docid
,MAX(CASE WHEN row = 1 THEN fieldvalue ELSE '' END) AS 'LastPrintPerson'
,MAX(CASE WHEN row = 2 THEN fieldvalue ELSE '' END) AS 'LastPrintDate'
FROM 
(
	SELECT docid,fieldname,fieldvalue,ROW_NUMBER() OVER(PARTITION BY docid ORDER BY docid) AS row
		FROM @t
) AS x
GROUP BY docid

---

Abdallah, PMP, MCTS

Answer 3

Hi 
it worked in both wasy ..thank you two lot,
but i have a small question to ask from "Abdshall"
basically what this line means ?

"OVER(PARTITION BY docid ORDER BY docid) AS row     "   i never used this before..could you please explain me bit me on this ?

thanks
regards
sukavi

Answer 4

This line  ROW_NUMBER() OVER(PARTITION BY docid ORDER BY docid) AS row is a ranking function that partitions the result based on the docid .

Run the following code by itself and you will see exactly what it does. You can change @t to your table name if you like.

SELECT docid,fieldname,fieldvalue,ROW_NUMBER() OVER(PARTITION BY docid ORDER BY docid) AS row
FROM @t

---

Abdallah, PMP, MCTS

Answer 5

Hi "Abdshall "

Thank you lot i understood nicely with your example...

great work mate.....

regards
sukavi .

Answer 6

Hi Abdshall,

i have got a small issue on your solution, according to the solution you provided ,when evever we add a new RowField we have to go and chage our SQL in order effect the changes,

for example if we add a new row like "LastModifiedDate" ,
 
Id     DocId    FieldName              FieldValue
1       201       LastPrintPerson      Dominic lorenzo
2       201       LastPrintDate         01/02/2007
3       201       LastModifiedDate   01/02/2006
4       204       LastPrintPerson      Gomez Hwang
5       204       LastPrintDate         01/07/2008
6       201       LastModifiedDate   01/02/2007

then we have to change our SQL like bellow
,
================================================
SELECT docid
,MAX(CASE WHEN row = 1 THEN fieldvalue ELSE '' END) AS 'LastPrintPerson'
,MAX(CASE WHEN row = 2 THEN fieldvalue ELSE '' END) AS 'LastPrintDate'
,MAX(CASE WHEN row = 3 THEN fieldvalue ELSE '' END) AS 'LastModifiedDate'

FROM
(
SELECT docid,fieldname,fieldvalue,ROW_NUMBER() OVER(PARTITION BY docid ORDER BY docid) AS row
FROM @t
) AS x
GROUP BY docid
===============================================

so instead of modifying the SQL each time ,is there any solution on this issue like writing a generic (dynamic) SQL so that we don't need to change our SQL each time, problem is my Table Data get change most of the time....so i have to change the SQL alone with that modification, bit of a pain .

any idea ,

 

regards

sukavi

 

Answer 7

In this case you might need to use dynamic pivot. Here are some helpful links for you .

http://www.sqlusa.com/bestpractices2005/dynamicpivot/
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
http://www.kodyaz.com/articles/t-sql-pivot-tables-in-sql-server-tutorial-with-examples.aspx

---

Abdallah, PMP, MCTS

Answer 8

Hi "Abdshall"
thanks for the quick reply....i am still working on this...basically trying to understand the articles u providd me...when i find the answer for this i will post it here....so this might be usefull for others.
thanks
regards
sukavi

Answer 9

I would suggest you start with the last one as it starts from regular pivot(in case you never worked with them) until it gets to the dynamic pivot.
Good luck

---

Abdallah, PMP, MCTS

Answer 10

Hi Abdshall,
i am trying from yesterday ,but still i am stuck.....i tried PIVOT solution.getting errors when i am trying using SUM ,thing is my "FieldValue" is a Varchar data type ..so it won't allow me to SUM up the results based on the Varchar datatype

eg : i tried in this way

DECLARE

 

@t TABLE(id INT IDENTITY(1,1),DocID INT,FieldName VARCHAR(MAX),FieldValue VARCHAR(MAX))

INSERT

 

INTO @t (DocID,FieldName,Fieldvalue)

SELECT

 

201,'LastPrintPerson','Dominic lorenzo' UNION ALL

SELECT

 

201,'LastPrintDate','01/02/2007' UNION ALL

SELECT

 

204 ,'LastPrintPerson','Gomez Hwang' UNION ALL

SELECT

 

204,'LastPrintDate','01/07/2008'

 

select

 

*

from

(

 

select

DocID

, FieldName, Fieldvalue

 

from @t

)

 

DataTable

PIVOT

(

 

SUM(Fieldvalue)

 

FOR FieldName

 

IN (

[LastPrintPerson]

 

)

)

 

PivotTable

 

but i am getting bellow error :

Operand data type varchar(max) is invalid for sum operator.

any solution on this?

 

 

thnaks
regards
sukavi

Answer 11

Change the SUM to COUNT and your query will work.

---

Abdallah, PMP, ITIL, MCTS

Answer 12

Hi thanks for the quick reply,
if i use Count ...it will only show me the number value, alwasys 1 if there is any values on it otherwise 0 ,
like :

docid    lastprintperson
201       1
204       1

but i really need to see the actual data ...
like  bellow ,

docid    lastprintperson

201       Dominic lorenzo
204       Gomez Hwang

how do i change 1  or zero in to its actual value ?

 

 

any idea ?
regards
sukavi

Answer 13

change it to MAX or MIN then

---

Abdallah, PMP, ITIL, MCTS

Answer 14

Hi .

I think there might be an issue on  MAX or MIN ,
becasue that sql only work  on small set of data,

for example original table that i am working on has got around "57822"  records
so when i run the SQL it take ages to run.......and still its running without any results....

any idea ?
regards
sukavi

Answer 15

Sukavi

58000 records is not much. Are you sure you have proper indexes on your table?
If you post all the query then we might be able to help you.

---

Abdallah, PMP, ITIL, MCTS

Answer 16

Hi Abdshall,
ye i do agree with you cos 58000 is not a massive amount of data,  but the problem is becasue of the data type we cant even do the Idexes...cos Varchar(Max) is not a valid indexes datatype right ?
bellow is the original sql that i am trying to run in my DB

DECLARE

 

@PivotColumnHeaders VARCHAR(MAX)

SELECT

 

@PivotColumnHeaders =

 

COALESCE(

@PivotColumnHeaders

+ ',[' + cast(KeyName as varchar) + ']',

 

'[' + cast(KeyName as varchar)+ ']'

 

)

FROM

 

UserFields

 

DECLARE

 

@PivotTableSQL NVARCHAR(MAX)

SET

 

@PivotTableSQL = N'

select

*

from

(

select

DocID, UserField, Fieldvalue

from Metadata_UserFields

) DataTable

PIVOT

(

MIN(Fieldvalue)

FOR UserField

IN (

'

 

+ @PivotColumnHeaders + '

)

) PivotTable

'

EXECUTE

 

(@PivotTableSQL)

Actually to run that SQL it took 55 Min  ....which was not acceptable........


i am really stuck now....dont know what to do
thanks
rergards
sukavi

Answer 17

Hi Abdshall,

Apart from the PIVOT...is there any other way to sort this problem or PIVOT is the only way to solve my problem ?

thanks
regards
sukavi

Answer 18

Can you provide the table structure? You have two tables in your code above, UserFields and MetaData_UserFields.
Actually PIVOT is the best way to go. There is always another way bu I'm not an expert with the SQL 2000.
As for the indexes, you can create index on varchar columns, but not varchar(max).

---

Abdallah, PMP, ITIL, MCTS

Answer 19

UserFields
ID             = BigInt
KeyName   =  Varchar(100)


Metadata_UserFields

ID          = bigint 
DocID    = bigint
UserField  = varchar(100) 
FieldValue  = nvarchar(MAX) 

Answer 20

I think becasue of the Varchar(Max) datatype its giving us problems....like take ages to run.... cos Aggregate fuctions works faster way when it comes to Decimal or int type of datatype......but in my case i keep the values as a Varchar(max)  ...for me only varchar(max) is the datatype that i can keep.

so in this case i have to forget about the PIVOT for the movment and start looking at any other solution ? what do u think ?