Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Help

Author  Topic 

ssunny
Posting Yak Master

133 Posts

Posted - 2013-02-04 : 15:14:23

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

133 Posts

Posted - 2013-02-04 : 15:42:11
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.
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-04 : 20:11:59
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

52326 Posts

Posted - 2013-02-04 : 23:04:52
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

133 Posts

Posted - 2013-02-05 : 10:12:52
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

52326 Posts

Posted - 2013-02-05 : 10:34:14
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

133 Posts

Posted - 2013-02-05 : 15:42:28
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

52326 Posts

Posted - 2013-02-06 : 00:48:58
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

133 Posts

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-06 : 22:46:30
welcome

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

Go to Top of Page
   

- Advertisement -