sql server db select query

Hi

I how to fetch the data in sql server database like below

Ex:
I have a table t1 with two columns item and desc like below

item desc
---- ----
1    red
1    small
1    think
2    white
2    large


I need to fetch the data as below

item desc1 desc2 desc3 desc4
1    red   small think  null
2    white large null  null

I am using sql server database.

Please provide both the querys for sql server databse 2005 and 2008 versions if queries need to write differently in these versions.

Thanks.
GouthamAnandAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
>>But can you please suggest the case of static.  
apologies, I missed this request. The following is static and assumes at most 4 columns
WITH CTE
AS (
	SELECT
		  table1.item
		-- , table2.whatever -- add fields as needed
		, table1.[desc]
		, row_number() OVER (
			PARTITION BY table1.Item ORDER BY table1.[desc]
			) AS row_ref
	FROM table1
	-- INNER JOIN table2 on table1.id = table2.fk
	-- WHERE 1 = 1 /* i.e. if you need to filter the data, do it here */
	)
SELECT
	  Item
        --, whatever
	, max(CASE WHEN row_ref = 1 THEN [desc] ELSE NULL END) AS Desc1
	, max(CASE WHEN row_ref = 2 THEN [desc] ELSE NULL END) AS Desc2
	, max(CASE WHEN row_ref = 3 THEN [desc] ELSE NULL END) AS Desc3
	, max(CASE WHEN row_ref = 4 THEN [desc] ELSE NULL END) AS Desc4
FROM CTE
GROUP BY
   Item
--, whatever

Open in new window

http://sqlfiddle.com/#!3/2cd6f/10

If you are adding further tables and fields, you can gather the base data within the CTE just like a normal select. Once you have that done then you also need to amend the selection list and group by list of the lower query.

If you are having issues with this please let us know.

Also, just another thing, in your sample data you use the field name [desc] "DESC" is used in SQL to direct an order by so if the field name [desc] is really what you use please ensure it is surround by the square brackets.

and yes, group_concat() is MySQL only - it does not work in sql server. (I did mention this.)
0
 
PortletPaulfreelancerCommented:
does this need to be dynamic? i.e. will the filtering change?
0
 
Ahmed HusseinCommented:
SELECT item ,GROUP_CONCAT(type_count) FROM
(SELECT item,desc,CONCAT(desc,",",COUNT(*)) as type_count
FROM TABLE_NAME GROUP BY item,desc) as x  
GROUP BY item;
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
PortletPaulfreelancerCommented:
see this working at: http://sqlfiddle.com/#!3/f26c7/1 
DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[desc]) 
            FROM table1 c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT item, ' + @cols + ' from 
            (
                select item, [desc]
                from table1
           ) x
            pivot 
            (
                 max([desc])
                for [desc] in (' + @cols + ')
            ) p '

--select @query
execute(@query)

Open in new window

it uses the PIVOT command
0
 
PortletPaulfreelancerCommented:
if you have trouble using this in sql 2005 I have another approach that will work in both using case expressions and group by
0
 
PortletPaulfreelancerCommented:
think group_concat is for MySQL (only)
0
 
GouthamAnandAuthor Commented:
Thank you for your responses.
But can you please suggest the case of static.   Means we assume that thre will be not more than 4 descriptions. ie 4 or less values only for any item.

Thanks.
0
 
GouthamAnandAuthor Commented:
Also, request you to provide a select query only as I need to join other tables and columns also for my original query. This requirement is part of my actual required query.
0
 
GouthamAnandAuthor Commented:
Group_concat is not working in sql server database.
0
 
PortletPaulfreelancerCommented:
just re-looked at your expected results, this one produces the following:
ITEM   DESC1  DESC2    DESC3
1      red    small    think
2      large  white    (null)

see: http://sqlfiddle.com/#!3/2cd6f/8 
DECLARE @query  AS NVARCHAR(MAX)

;WITH
COLS AS (
          SELECT
              ', max(CASE WHEN row_ref = '
            + convert(varchar,number)
            + ' THEN [desc] ELSE null END) AS Desc'
            + convert(varchar,number)
            AS casea
          FROM master.dbo.spt_values
          WHERE  TYPE = 'P'
          AND number BETWEEN 1 AND (
                                    SELECT max(c)
                                    FROM (
                                          SELECT
                                                item
                                              , count(*) AS c
                                          FROM table1
                                          WHERE 1=1  /* i.e. filter the data here */
                                          GROUP BY
                                                item
                                        ) AS x
                                    )
           )


select @query = 

  'WITH CTE as ('
+ '        select'
+ '            table1.item, table1.[desc]'
+ '          , row_number() over (partition by table1.Item order by table1.[desc]) as row_ref'
+ '        from table1'
+ '        where 1=1 /* i.e. filter the data here */'
+ '       )'
+ 'select Item, ' 
+ STUFF( 
           ( SELECT casea
             FROM cols
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')  
+ ' from CTE group by Item'

--select @query -- uncomment to inspect what the variable contains
exec(@query)
;

Open in new window

0
 
GouthamAnandAuthor Commented:
Thank you very much. I change the script accordingly to my requirement and  it works perfectly for my requirement.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.