SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ssunny
Posting Yak Master

USA
133 Posts

Posted - 02/04/2013 :  15:14:23  Show Profile  Reply with Quote

Hello Gurus,

I need help with following query.

-- table structure with sample data

declare @table table (
id int,
name varchar (100),
value varchar (100)
)

insert into @table
values (1,'brand','apple')
insert into @table
values (1,'category','phone')
insert into @table
values (1,'carrier','at&t')
insert into @table
values (1,'carrier','sprint')
insert into @table
values (1,'color','white')
insert into @table
values (1,'carrier','verizon')
insert into @table
values (1,'model','iPhone5')
insert into @table
values (1,'screen','4')
insert into @table
values (1,'processor','A6')
insert into @table
values (1,'color','black')

select * from @table

-- current output

id name value
1 brand apple
1 category phone
1 carrier at&t
1 carrier sprint
1 color white
1 carrier verizon
1 model iPhone5
1 screen 4
1 processor A6
1 color black

-- expected output

id brand category carrier carrier color carrier model screen processor color

1 apple phone at&t sprint white verizon iPhone5 4 A6 black

Thanks.

ssunny
Posting Yak Master

USA
133 Posts

Posted - 02/04/2013 :  15:42:11  Show Profile  Reply with Quote
Ultimately I like to insert my expected output into a table.

insert into @table1
-- expected output

And I think I need a dynamic pivot here because id 2 can have more/less name-value combination than id 1.

Thanks.

Edited by - ssunny on 02/04/2013 17:32:00
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/04/2013 :  20:11:59  Show Profile  Reply with Quote
I'm not sure that'll work because you cannot have more than one column in a set that has the same name. Obviously you can select columns and alias them to the same name, but it doesn't really make much sense. So, in this case, is there any chance you can fix your data model?

At any rate, here is how to do a pivot, but it probably doesn't really help in this case:
SELECT *
FROM @table
PIVOT
(
	MIN(Value)
	FOR Name IN 
		(
			[brand]
			,[category]
			,[carrier]
			--,[carrier]
			,[color]
			--,[carrier]
			,[model]
			,[screen]
			,[processor]
			--,[color]
		)
) AS PivotTable
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/04/2013 :  23:04:52  Show Profile  Reply with Quote
other way if you cant change datamodel is to group similar column contents onto a single column and then do pivot over it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ssunny
Posting Yak Master

USA
133 Posts

Posted - 02/05/2013 :  10:12:52  Show Profile  Reply with Quote
Lamprey/Visakh,

Thank you for reply.Lamprey, I understand your point but not sure if I would be able to change the data format because ultimately I want to feed this data into solr.

Let's say in solr, I have a schema defined for <carrier>. Now in this case if I combine all carrier into a single column , it will be like this:

1 carrier at@t,sprint,verizon

But I don't know if solr will index that combination and yet consider them as different facet values.

I will see what can I do.
Thank you again guys.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/05/2013 :  10:34:14  Show Profile  Reply with Quote
see illustration below


create  table [tbl](
id int,
name varchar (100),
value varchar (100)
)

insert into tbl
values (1,'brand','apple')
insert into tbl
values (1,'category','phone')
insert into tbl
values (1,'carrier','at&t')
insert into tbl
values (1,'carrier','sprint')
insert into tbl
values (1,'color','white')
insert into tbl
values (1,'carrier','verizon')
insert into tbl
values (1,'model','iPhone5')
insert into tbl
values (1,'screen','4')
insert into tbl
values (1,'processor','A6')
insert into tbl
values (1,'color','black')


declare @pivotlist varchar(max)
select @pivotlist = 
stuff((select distinct  ',[' +  name + ']'
from tbl
for xml path('')),1,1,'')

declare @str varchar(1000)

SET @str='
;with CTE
AS
(
select t.*,
STUFF((SELECT '','' + replace(value,''&'',''|'') from tbl WHERE id = t.id AND name = t.name for xml path('''')),1,1,'''') AS valuelist
from (select distinct ID, name from tbl)t
) 
SELECT *
FROM
(
SELECT id,name,REPLACE(valuelist,''|'',''&'') AS valuelist
FROM CTE
)t
PIVOT (MAX(valuelist) FOR name IN (' + @pivotlist + '))p'

EXEC(@str)


output
---------------------------------------------------------------------------------------------------
id	brand	carrier	               category	 color	        model	  processor	screen
---------------------------------------------------------------------------------------------------
1	apple	at&t,sprint,verizon	phone	 white,black	iPhone5	  A6	        4



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ssunny
Posting Yak Master

USA
133 Posts

Posted - 02/05/2013 :  15:42:28  Show Profile  Reply with Quote
Visakh,

Thank you. It works great per my need. Is there a way to write same thing without pivot (sql 2000 way) also? I need same thing for one other server also which is running sql 2000.

Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/06/2013 :  00:48:58  Show Profile  Reply with Quote
yep...you can do that as well

see

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ssunny
Posting Yak Master

USA
133 Posts

Posted - 02/06/2013 :  12:01:38  Show Profile  Reply with Quote
I will try and let you know Visakh. Thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/06/2013 :  22:46:30  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000